/*
{*******************************************************************}
{                                                                   }
{       KS-GANTT DotNet Library                                     }
{       Gantt-Charting Control for Windows Forms                    }
{                                                                   }
{       Copyright (c) 2009 - 2012 by Kroll-Software                 }
{       All Rights Reserved                                         }
{                                                                   }
{   This code is provided 'as is',                                  }
{   without warranties of any kind. Use at your own risk.           }                                                              
{                                                                   }
{   As a license holder of the KS-Gantt Control,                    }
{   you may freely redistribute executables based on                }
{   and created with these contents.                                }
{   You can modify the source code to meet your needs.              }
{                                                                   }
{   However, you must not publish, redistribute or transfer         }
{   this source code to 3rd parties under any conditions.           }
{                                                                   } 
{*******************************************************************}
*/

using System;
using System.Collections.Generic;
using System.Text;
using KS.Common;
using KS.Gantt;
using System.Data;

namespace MiGuava.SqlServer
{
    public class SqlDBTextEventArgs : EventArgs
    {
        protected string m_Text = "";

        public SqlDBTextEventArgs(string text)
            : base()
        {
            m_Text = text;
        }

        public string Text
        {
            get
            {
                return m_Text;
            }            
        }
    }

    public class GanttSqlDB : SqlDatabase, IDisposable
    {
        protected string m_SqlCreateScriptPath = "";
        public string SqlCreateScriptPath
        {
            get
            {
                return m_SqlCreateScriptPath;
            }
            set
            {
                m_SqlCreateScriptPath = value;
            }
        }

        protected string m_SqlUpdateScriptPath = "";
        public string SqlUpdateScriptPath
        {
            get
            {
                return m_SqlUpdateScriptPath;
            }
            set
            {
                m_SqlUpdateScriptPath = value;
            }
        }

        public delegate void ScriptStatusEventHandler(object sender, SqlDBTextEventArgs e);
        public event ScriptStatusEventHandler ScriptStatus = null;


        protected SQLBuilder sqlProjects = null;
        protected SQLBuilder sqlTasks = null;
        protected SQLBuilder sqlResources = null;        
        protected SQLBuilder sqlGroups = null;
        protected SQLBuilder sqlResourceRelations = null;        
        protected SQLBuilder sqlTaskRelations = null;
        protected SQLBuilder sqlResourceSkills = null;
        protected SQLBuilder sqlCalendars = null;
        protected SQLBuilder sqlCalendarWorkingHours = null;
        protected SQLBuilder sqlCalendarExceptions = null;
        protected SQLBuilder sqlSkills = null;
        protected SQLBuilder sqlWorks = null;
        protected SQLBuilder sqlWorkPreferredResources = null;        
        protected SQLBuilder sqlResourceAllocations = null;

        protected const DBMSTypes dbmsType = DBMSTypes.MSSQLServer;

        public string ProjectKey = "";

        public GanttSqlDB()
            : base()
        {
            InitSQLBuilders();
        }

        ~GanttSqlDB()
        {
            Dispose(false);
        }

        public bool BaseConnect()
        {
            return base.Connect();
        }

        public override bool Connect()
        {
            bool bRet = base.Connect();

            if (!CheckServerVersion())
            {
                string strException = "";

                try
                {
                    strException = String.Format(Properties.Resources.SQLInvalidVersionError, DBConn.ServerVersion);
                }
                catch (Exception)
                {
                    strException = String.Format(Properties.Resources.SQLInvalidVersionError, "unknown");
                }                

                Disconnect();
                this.DBCatalog = "";
                this.SaveSettings();
                throw new Exception(strException);
            }

            try
            {
                if (!CheckDatabaseVersion())
                {
                    Disconnect();
                    this.DBCatalog = "";
                    this.SaveSettings();
                    return false;
                }
            }
            catch (Exception ex)
            {
                Disconnect();
                this.DBCatalog = "";
                this.SaveSettings();
                throw ex;
            }            

            if (bRet)
                InitSQLBuilders();

            return bRet;
        }

        public override void Disconnect()
        {
            DisposeSQLObjects();
            base.Disconnect();
        }

        protected void InitSQLBuilders()
        {
            // *** Projects ***
            sqlProjects = new SQLBuilder("Projects", dbmsType);
            sqlProjects.SQLConn = this.DBConn;

            sqlProjects.AddColumn("ItemKey", SQLBuilderTypes.sqlString, false, 36);
            sqlProjects.AddColumn("ItemText", SQLBuilderTypes.sqlString);
            sqlProjects.AddColumn("Description", SQLBuilderTypes.sqlMemo);
            sqlProjects.AddColumn("Properties", SQLBuilderTypes.sqlMemo);

            sqlProjects.AddColumn("Granularity", SQLBuilderTypes.sqlInt);
            sqlProjects.AddColumn("MinMaxLinesVisible", SQLBuilderTypes.sqlBool);
            sqlProjects.AddColumn("MinMaxLineStyle", SQLBuilderTypes.sqlMemo);
            sqlProjects.AddColumn("TodayLineVisible", SQLBuilderTypes.sqlBool);
            sqlProjects.AddColumn("TodayLineStyle", SQLBuilderTypes.sqlMemo);

            sqlProjects.AddColumn("ShowWeekends", SQLBuilderTypes.sqlBool);
            sqlProjects.AddColumn("WeekendFormatStyle", SQLBuilderTypes.sqlMemo);

            sqlProjects.AddColumn("ShowWorkingHours", SQLBuilderTypes.sqlBool);
            sqlProjects.AddColumn("ShowWeeks", SQLBuilderTypes.sqlBool);
            sqlProjects.AddColumn("GridLines", SQLBuilderTypes.sqlBool);

            sqlProjects.AddColumn("GridLineStyle", SQLBuilderTypes.sqlMemo);
            sqlProjects.AddColumn("DefaultBarFormatStyle", SQLBuilderTypes.sqlMemo);
            sqlProjects.AddColumn("DefaultGroupLineStyle", SQLBuilderTypes.sqlMemo);
            sqlProjects.AddColumn("ResourceBarStyle", SQLBuilderTypes.sqlMemo);
            sqlProjects.AddColumn("RelationLineStyle", SQLBuilderTypes.sqlMemo);
            sqlProjects.AddColumn("RelationLineArrowWidth", SQLBuilderTypes.sqlInt);

            sqlProjects.AddColumn("ShowCriticalPath", SQLBuilderTypes.sqlBool);
            sqlProjects.AddColumn("CriticalPathLineStyle", SQLBuilderTypes.sqlMemo);

            sqlProjects.AddColumn("BarTooltips", SQLBuilderTypes.sqlInt);
            sqlProjects.AddColumn("ItemTextDisplay", SQLBuilderTypes.sqlInt);

            sqlProjects.AddColumn("BarStyle", SQLBuilderTypes.sqlInt);

            sqlProjects.AddColumn("BarHeight", SQLBuilderTypes.sqlInt);
            sqlProjects.AddColumn("RowHeight", SQLBuilderTypes.sqlInt);

            sqlProjects.AddColumn("PercentBarDeflateX", SQLBuilderTypes.sqlInt);
            sqlProjects.AddColumn("PercentBarDeflateY", SQLBuilderTypes.sqlInt);

            sqlProjects.AddColumn("RandomBarColors", SQLBuilderTypes.sqlBool);

            sqlProjects.AddColumn("ResourceBarStyleMode", SQLBuilderTypes.sqlInt);
            sqlProjects.AddColumn("ResourceViewFilter", SQLBuilderTypes.sqlInt);

            sqlProjects.AddColumn("ResourceStandardColor", SQLBuilderTypes.sqlString);
            sqlProjects.AddColumn("ResourceOverloadColor", SQLBuilderTypes.sqlString);
            sqlProjects.AddColumn("ResourceUnderloadColor", SQLBuilderTypes.sqlString);

            sqlProjects.AddColumn("ShowIcons", SQLBuilderTypes.sqlBool);
            sqlProjects.AddColumn("ShowPriorityIcons", SQLBuilderTypes.sqlBool);
            sqlProjects.AddColumn("ShowStatusIcons", SQLBuilderTypes.sqlBool);
            sqlProjects.AddColumn("ShowOverDueIcon", SQLBuilderTypes.sqlBool);
            sqlProjects.AddColumn("ShowHappinessIcon", SQLBuilderTypes.sqlBool);            
            sqlProjects.AddColumn("ShowAlignmentIcon", SQLBuilderTypes.sqlBool);
            sqlProjects.AddColumn("ShowCustomIcons", SQLBuilderTypes.sqlBool);

            sqlProjects.AddColumn("StartDate", SQLBuilderTypes.sqlDate);
            sqlProjects.AddColumn("EndDate", SQLBuilderTypes.sqlDate);

            sqlProjects.AddColumn("DefaultTaskType", SQLBuilderTypes.sqlInt);
            sqlProjects.AddColumn("DefaultDurationType", SQLBuilderTypes.sqlInt);
            sqlProjects.AddColumn("NewTaskCount", SQLBuilderTypes.sqlInt);
            sqlProjects.AddColumn("ColorCyclerCurrentIndex", SQLBuilderTypes.sqlInt);
            sqlProjects.AddColumn("ScrollOffsetY", SQLBuilderTypes.sqlInt);

            sqlProjects.AddColumn("ResourceBarColorTolerance", SQLBuilderTypes.sqlFloat, false);
            

            // *** Tasks ***
            sqlTasks = new SQLBuilder("Tasks", dbmsType);
            sqlTasks.SQLConn = this.DBConn;

            sqlTasks.AddColumn("ItemKey", SQLBuilderTypes.sqlString, false, 36);
            sqlTasks.AddColumn("ProjectKey", SQLBuilderTypes.sqlString, false, 36);
            sqlTasks.AddColumn("ItemText", SQLBuilderTypes.sqlString);
            sqlTasks.AddColumn("Description", SQLBuilderTypes.sqlMemo);
            sqlTasks.AddColumn("Visible", SQLBuilderTypes.sqlBool);
            sqlTasks.AddColumn("StartDate", SQLBuilderTypes.sqlDate, false);
            sqlTasks.AddColumn("EndDate", SQLBuilderTypes.sqlDate, false);
            sqlTasks.AddColumn("Properties", SQLBuilderTypes.sqlMemo);
            sqlTasks.AddColumn("FormatStyle", SQLBuilderTypes.sqlMemo);
            sqlTasks.AddColumn("ParentItemKey", SQLBuilderTypes.sqlString, true, 36);
            sqlTasks.AddColumn("PercentDone", SQLBuilderTypes.sqlFloat);
            sqlTasks.AddColumn("EarliestStartDate", SQLBuilderTypes.sqlDate, true);
            sqlTasks.AddColumn("LatestStartDate", SQLBuilderTypes.sqlDate, true);
            sqlTasks.AddColumn("EarliestEndDate", SQLBuilderTypes.sqlDate, true);
            sqlTasks.AddColumn("LatestEndDate", SQLBuilderTypes.sqlDate, true);
            sqlTasks.AddColumn("StartDateRule", SQLBuilderTypes.sqlInt);
            sqlTasks.AddColumn("EndDateRule", SQLBuilderTypes.sqlInt);
            sqlTasks.AddColumn("IsMilestone", SQLBuilderTypes.sqlBool);
            sqlTasks.AddColumn("Priority", SQLBuilderTypes.sqlInt);
            sqlTasks.AddColumn("Status", SQLBuilderTypes.sqlInt);                       
            sqlTasks.AddColumn("TaskType", SQLBuilderTypes.sqlInt);
            sqlTasks.AddColumn("Estimated", SQLBuilderTypes.sqlBool);
            sqlTasks.AddColumn("DurationType", SQLBuilderTypes.sqlInt);
            sqlTasks.AddColumn("DurationDays", SQLBuilderTypes.sqlFloat);
            sqlTasks.AddColumn("CalendarKey", SQLBuilderTypes.sqlString, true, 36);
            sqlTasks.AddColumn("ProjectCalendarCombinationMode", SQLBuilderTypes.sqlInt);
            sqlTasks.AddColumn("ASAP", SQLBuilderTypes.sqlBool);

            // *** Groups ***
            sqlGroups = new SQLBuilder("Groups", dbmsType);
            sqlGroups.SQLConn = this.DBConn;

            sqlGroups.AddColumn("ItemKey", SQLBuilderTypes.sqlString, false, 36);
            sqlGroups.AddColumn("ProjectKey", SQLBuilderTypes.sqlString, false, 36);
            sqlGroups.AddColumn("ItemText", SQLBuilderTypes.sqlString);
            sqlGroups.AddColumn("Description", SQLBuilderTypes.sqlMemo);
            sqlGroups.AddColumn("Visible", SQLBuilderTypes.sqlBool);
            sqlGroups.AddColumn("Properties", SQLBuilderTypes.sqlMemo);
            sqlGroups.AddColumn("FormatStyle", SQLBuilderTypes.sqlMemo);
            sqlGroups.AddColumn("ParentItemKey", SQLBuilderTypes.sqlString, true, 36);

            // *** Resources ***
            sqlResources = new SQLBuilder("Resources", dbmsType);
            sqlResources.SQLConn = this.DBConn;

            sqlResources.AddColumn("ItemKey", SQLBuilderTypes.sqlString, false, 36);
            sqlResources.AddColumn("ProjectKey", SQLBuilderTypes.sqlString, true, 36);
            sqlResources.AddColumn("ItemText", SQLBuilderTypes.sqlString);
            sqlResources.AddColumn("Description", SQLBuilderTypes.sqlMemo);
            sqlResources.AddColumn("Visible", SQLBuilderTypes.sqlBool);
            sqlResources.AddColumn("Properties", SQLBuilderTypes.sqlMemo);
            sqlResources.AddColumn("ResourceType", SQLBuilderTypes.sqlInt);
            sqlResources.AddColumn("Role", SQLBuilderTypes.sqlString);
            sqlResources.AddColumn("CostPerDay", SQLBuilderTypes.sqlMoney);

            sqlResources.AddColumn("CalendarKey", SQLBuilderTypes.sqlString, true, 36);
            sqlResources.AddColumn("ProjectCalendarCombinationMode", SQLBuilderTypes.sqlInt);
            sqlResources.AddColumn("TaskCalendarCombinationMode", SQLBuilderTypes.sqlInt);


            // *** ResourceRelations ***
            sqlResourceRelations = new SQLBuilder("ResourceRelations", dbmsType);
            sqlResourceRelations.SQLConn = this.DBConn;

            sqlResourceRelations.AddColumn("TaskKey", SQLBuilderTypes.sqlString, false, 36);
            sqlResourceRelations.AddColumn("ProjectKey", SQLBuilderTypes.sqlString, false, 36);
            sqlResourceRelations.AddColumn("ResourceKey", SQLBuilderTypes.sqlString, false, 36);
            sqlResourceRelations.AddColumn("ProposedUnit", SQLBuilderTypes.sqlFloat, false);
            sqlResourceRelations.AddColumn("Unit", SQLBuilderTypes.sqlFloat, false);            
            sqlResourceRelations.AddColumn("Responsible", SQLBuilderTypes.sqlBool, false);            


            // *** TaskRelations ***
            sqlTaskRelations = new SQLBuilder("TaskRelations", dbmsType);
            sqlTaskRelations.SQLConn = this.DBConn;

            sqlTaskRelations.AddColumn("TaskKey", SQLBuilderTypes.sqlString, false, 36);
            sqlTaskRelations.AddColumn("DependingTaskKey", SQLBuilderTypes.sqlString, false, 36);
            sqlTaskRelations.AddColumn("ProjectKey", SQLBuilderTypes.sqlString, false, 36);
            sqlTaskRelations.AddColumn("DelayDays", SQLBuilderTypes.sqlInt, false);
            sqlTaskRelations.AddColumn("DelayDurationType", SQLBuilderTypes.sqlInt, false);
            sqlTaskRelations.AddColumn("TaskRelationMode", SQLBuilderTypes.sqlInt, false);
            sqlTaskRelations.AddColumn("TaskRelationType", SQLBuilderTypes.sqlInt, false);


            // *** ResourceSkills ***
            sqlResourceSkills = new SQLBuilder("ResourceSkills", dbmsType);
            sqlResourceSkills.SQLConn = this.DBConn;

            sqlResourceSkills.AddColumn("ResourceKey", SQLBuilderTypes.sqlString, false, 36);
            sqlResourceSkills.AddColumn("SkillKey", SQLBuilderTypes.sqlString, false, 36);


            // Calendars
            sqlCalendars = new SQLBuilder("Calendars", dbmsType);
            sqlCalendars.SQLConn = this.DBConn;

            sqlCalendars.AddColumn("ItemKey", SQLBuilderTypes.sqlString, false, 36);
            sqlCalendars.AddColumn("ProjectKey", SQLBuilderTypes.sqlString, true, 36);     // Allow global: ProjectKey == null
            sqlCalendars.AddColumn("ItemText", SQLBuilderTypes.sqlString);
            sqlCalendars.AddColumn("Description", SQLBuilderTypes.sqlMemo);

            sqlCalendars.AddColumn("InheritedCalendarKey", SQLBuilderTypes.sqlString, true, 36);
            sqlCalendars.AddColumn("IsResourceCalendar", SQLBuilderTypes.sqlBool, false, 1, false);
            sqlCalendars.AddColumn("IsTaskCalendar", SQLBuilderTypes.sqlBool, false, 1, false);

            sqlCalendars.AddColumn("HasWorkingHours", SQLBuilderTypes.sqlBool, false, 1, false);
            sqlCalendars.AddColumn("WorkMonday", SQLBuilderTypes.sqlBool, false, 1, false);
            sqlCalendars.AddColumn("WorkTuesday", SQLBuilderTypes.sqlBool, false, 1, false);
            sqlCalendars.AddColumn("WorkWednesday", SQLBuilderTypes.sqlBool, false, 1, false);
            sqlCalendars.AddColumn("WorkThursday", SQLBuilderTypes.sqlBool, false, 1, false);
            sqlCalendars.AddColumn("WorkFriday", SQLBuilderTypes.sqlBool, false, 1, false);
            sqlCalendars.AddColumn("WorkSaturday", SQLBuilderTypes.sqlBool, false, 1, false);
            sqlCalendars.AddColumn("WorkSunday", SQLBuilderTypes.sqlBool, false, 1, false);

            // CalendarWorkingHours
            sqlCalendarWorkingHours = new SQLBuilder("CalendarWorkingHours", dbmsType);
            sqlCalendarWorkingHours.SQLConn = this.DBConn;

            sqlCalendarWorkingHours.AddColumn("CalendarKey", SQLBuilderTypes.sqlString, false, 36);
            sqlCalendarWorkingHours.AddColumn("DayOfWeek", SQLBuilderTypes.sqlInt, false, 1, 0);
            sqlCalendarWorkingHours.AddColumn("StartHour", SQLBuilderTypes.sqlFloat, false, 1, 0);
            sqlCalendarWorkingHours.AddColumn("EndHour", SQLBuilderTypes.sqlFloat, false, 1, 0);
            sqlCalendarWorkingHours.AddColumn("ExceptionDate", SQLBuilderTypes.sqlDate, true, 1, DBNull.Value);

            // CalendarExceptions
            sqlCalendarExceptions = new SQLBuilder("CalendarExceptions", dbmsType);
            sqlCalendarExceptions.SQLConn = this.DBConn;

            sqlCalendarExceptions.AddColumn("CalendarKey", SQLBuilderTypes.sqlString, false, 36);
            sqlCalendarExceptions.AddColumn("ExceptionDate", SQLBuilderTypes.sqlDate, false, 1, DBNull.Value);
            sqlCalendarExceptions.AddColumn("IsWorkingDay", SQLBuilderTypes.sqlBool, false, 1, true);            

            // Skills            
            sqlSkills = new SQLBuilder("Skills", dbmsType);
            sqlSkills.SQLConn = this.DBConn;

            sqlSkills.AddColumn("ItemKey", SQLBuilderTypes.sqlString, false, 36);
            sqlSkills.AddColumn("ProjectKey", SQLBuilderTypes.sqlString, true, 36);     // Allow global: ProjectKey == null
            sqlSkills.AddColumn("ItemText", SQLBuilderTypes.sqlString);
            sqlSkills.AddColumn("Description", SQLBuilderTypes.sqlMemo);
            sqlSkills.AddColumn("Productivity", SQLBuilderTypes.sqlFloat, false, 1, 1.0);

            // Works
            sqlWorks = new SQLBuilder("Works", dbmsType);
            sqlWorks.SQLConn = this.DBConn;

            sqlWorks.AddColumn("ItemKey", SQLBuilderTypes.sqlString, false, 36);
            sqlWorks.AddColumn("TaskKey", SQLBuilderTypes.sqlString, false, 36);
            sqlWorks.AddColumn("ProjectKey", SQLBuilderTypes.sqlString, false, 36);
            sqlWorks.AddColumn("ItemText", SQLBuilderTypes.sqlString);
            sqlWorks.AddColumn("Description", SQLBuilderTypes.sqlMemo);

            sqlWorks.AddColumn("ProposedWorkHours", SQLBuilderTypes.sqlFloat);
            sqlWorks.AddColumn("WorkHours", SQLBuilderTypes.sqlFloat);            
            sqlWorks.AddColumn("SkillKey", SQLBuilderTypes.sqlString, true, 36);    // SkillKey can be null
            sqlWorks.AddColumn("MaxEntities", SQLBuilderTypes.sqlInt);

            // WorkPreferredResources
            sqlWorkPreferredResources = new SQLBuilder("WorkPreferredResources", dbmsType);
            sqlWorkPreferredResources.SQLConn = this.DBConn;

            sqlWorkPreferredResources.AddColumn("WorkKey", SQLBuilderTypes.sqlString, false, 36);
            sqlWorkPreferredResources.AddColumn("ResourceKey", SQLBuilderTypes.sqlString, false, 36);

            // ResourceAllocations
            sqlResourceAllocations = new SQLBuilder("ResourceAllocations", dbmsType);
            sqlResourceAllocations.SQLConn = this.DBConn;
            
            sqlResourceAllocations.AddColumn("ResourceKey", SQLBuilderTypes.sqlString, false, 36);
            sqlResourceAllocations.AddColumn("TaskKey", SQLBuilderTypes.sqlString, false, 36);
            sqlResourceAllocations.AddColumn("TaskCalendarKey", SQLBuilderTypes.sqlString, true, 36);
            sqlResourceAllocations.AddColumn("TaskCalendarCombinationMode", SQLBuilderTypes.sqlInt, false);
            sqlResourceAllocations.AddColumn("ProjectKey", SQLBuilderTypes.sqlString, false, 36);    // ResourceAllocation must be aligned to a Project
            sqlResourceAllocations.AddColumn("StartDate", SQLBuilderTypes.sqlDate);
            sqlResourceAllocations.AddColumn("EndDate", SQLBuilderTypes.sqlDate);
            sqlResourceAllocations.AddColumn("Unit", SQLBuilderTypes.sqlFloat);
            sqlResourceAllocations.AddColumn("TaskText", SQLBuilderTypes.sqlString);
            sqlResourceAllocations.AddColumn("ProjectText", SQLBuilderTypes.sqlString);
        }

        protected void DisposeSQLObjects()
        {
            if (sqlProjects != null)
            {
                sqlProjects.Dispose();
                sqlProjects = null;
            }

            if (sqlTasks != null)
            {
                sqlTasks.Dispose();
                sqlTasks = null;
            }

            if (sqlResources != null)
            {
                sqlResources.Dispose();
                sqlResources = null;
            }

            if (sqlGroups != null)
            {
                sqlGroups.Dispose();
                sqlGroups = null;
            }

            if (sqlResourceRelations != null)
            {
                sqlResourceRelations.Dispose();
                sqlResourceRelations = null;
            }            

            if (sqlTaskRelations != null)
            {
                sqlTaskRelations.Dispose();
                sqlTaskRelations = null;
            }

            if (sqlResourceSkills != null)
            {
                sqlResourceSkills.Dispose();
                sqlResourceSkills = null;
            }

            if (sqlCalendars != null)
            {
                sqlCalendars.Dispose();
                sqlCalendars = null;
            }

            if (sqlCalendarWorkingHours != null)
            {
                sqlCalendarWorkingHours.Dispose();
                sqlCalendarWorkingHours = null;
            }

            if (sqlCalendarExceptions != null)
            {
                sqlCalendarExceptions.Dispose();
                sqlCalendarExceptions = null;
            }

            if (sqlSkills != null)
            {
                sqlSkills.Dispose();
                sqlSkills = null;
            }

            if (sqlWorks != null)
            {
                sqlWorks.Dispose();
                sqlWorks = null;
            }

            if (sqlWorkPreferredResources != null)
            {
                sqlWorkPreferredResources.Dispose();
                sqlWorkPreferredResources = null;
            }

            if (sqlResourceAllocations != null)
            {
                sqlResourceAllocations.Dispose();
                sqlResourceAllocations = null;
            }
        }
        
        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                // free managed resources                
                this.Disconnect();
            }

            base.Dispose(disposing);
        }

        // <<<<<<<<<<<<<<<<< DB Scope Functions >>>>>>>>>>>>>>>>>>>>

        public void ClearAll()
        {
            Clear("");
        }

        public void Clear(string strProjectKey)
        {
            string strWhere = "";
            if (strProjectKey != "")
            {
                strWhere = " WHERE (ProjectKey=" + Strings.ConvertNullSQL(strProjectKey) + ")";
            }

            this.ExecuteUpdateSQL("delete from groups" + strWhere);
            this.ExecuteUpdateSQL("delete from tasks" + strWhere);
            
            this.ExecuteUpdateSQL("delete from resources" + strWhere);
            this.ExecuteUpdateSQL("delete from calendars" + strWhere);
            this.ExecuteUpdateSQL("delete from skills" + strWhere);

            this.ExecuteUpdateSQL("delete from ResourceAllocations" + strWhere);
        }


        // <<<<<<<<<<<<<<<<< PROJECTS >>>>>>>>>>>>>>>>>>>>

        protected void SetValuesForProject(clsProject item)
        {
            sqlProjects.ResetData();
            sqlProjects.ResetParameters();
            sqlProjects.AddValue("ItemKey", item.Key);
            sqlProjects.AddValue("ItemText", item.Text);
            sqlProjects.AddValue("Description", item.Description);

            sqlProjects.AddValue("Granularity", (int)item.Granularity);
            sqlProjects.AddValue("MinMaxLinesVisible", item.MinMaxLinesVisible);
            sqlProjects.AddValue("MinMaxLineStyle", item.MinMaxLineStyle.GetXML());
            sqlProjects.AddValue("TodayLineVisible", item.TodayLineVisible);
            sqlProjects.AddValue("TodayLineStyle", item.TodayLineStyle.GetXML());

            sqlProjects.AddValue("ShowWeekends", item.ShowWeekends);
            sqlProjects.AddValue("WeekendFormatStyle", item.WeekendFormatStyle.GetXML());

            sqlProjects.AddValue("ShowWorkingHours", item.ShowWorkingHours);
            sqlProjects.AddValue("ShowWeeks", item.ShowWeeks);
            sqlProjects.AddValue("GridLines", item.GridLines);

            sqlProjects.AddValue("GridLineStyle", item.GridLineStyle.GetXML());
            sqlProjects.AddValue("DefaultBarFormatStyle", item.DefaultBarFormatStyle.GetXML());
            sqlProjects.AddValue("DefaultGroupLineStyle", item.DefaultGroupLineStyle.GetXML());
            sqlProjects.AddValue("ResourceBarStyle", item.ResourceBarStyle.GetXML());
            sqlProjects.AddValue("RelationLineStyle", item.RelationLineStyle.GetXML());
            sqlProjects.AddValue("RelationLineArrowWidth", item.RelationLineArrowWidth);

            sqlProjects.AddValue("ShowCriticalPath", item.ShowCriticalPath);
            sqlProjects.AddValue("CriticalPathLineStyle", item.CriticalPathLineStyle.GetXML());

            sqlProjects.AddValue("BarTooltips", (int)item.BarTooltips);
            sqlProjects.AddValue("ItemTextDisplay", (int)item.ItemTextDisplay);

            sqlProjects.AddValue("BarStyle", (int)item.BarStyle);

            sqlProjects.AddValue("BarHeight", item.BarHeight);
            sqlProjects.AddValue("RowHeight", item.RowHeight);

            sqlProjects.AddValue("PercentBarDeflateX", item.PercentBarDeflate.Width);
            sqlProjects.AddValue("PercentBarDeflateY", item.PercentBarDeflate.Height);

            sqlProjects.AddValue("RandomBarColors", item.RandomBarColors);

            sqlProjects.AddValue("ResourceBarStyleMode", (int)item.ResourceBarStyleMode);
            sqlProjects.AddValue("ResourceViewFilter", (int)item.ResourceViewFilter);

            sqlProjects.AddValue("ResourceStandardColor", item.ResourceStandardColorXML);
            sqlProjects.AddValue("ResourceOverloadColor", item.ResourceOverloadColorXML);
            sqlProjects.AddValue("ResourceUnderloadColor", item.ResourceUnderloadColorXML);

            sqlProjects.AddValue("ShowIcons", item.ShowIcons);
            sqlProjects.AddValue("ShowPriorityIcons", item.ShowPriorityIcons);
            sqlProjects.AddValue("ShowStatusIcons", item.ShowStatusIcons);
            sqlProjects.AddValue("ShowOverDueIcon", item.ShowOverDueIcon);
            sqlProjects.AddValue("ShowHappinessIcon", item.ShowHappinessIcon);            
            sqlProjects.AddValue("ShowAlignmentIcon", item.ShowAlignmentIcon);
            sqlProjects.AddValue("ShowCustomIcons", item.ShowCustomIcons);

            sqlProjects.AddValue("StartDate", item.StartDate);
            sqlProjects.AddValue("EndDate", item.EndDate);

            sqlProjects.AddValue("DefaultTaskType", (int)item.DefaultTaskType);
            sqlProjects.AddValue("DefaultDurationType", (int)item.DefaultDurationType);
            sqlProjects.AddValue("NewTaskCount", item.NewTaskCount);
            sqlProjects.AddValue("ColorCyclerCurrentIndex", item.ColorCyclerCurrentIndex);
            sqlProjects.AddValue("ScrollOffsetY", item.ScrollOffsetY);

            sqlProjects.AddValue("ResourceBarColorTolerance", item.ResourceBarColorTolerance);
            
            try
            {
                sqlProjects.AddValue("Properties", item.GetPropertiesXML());
            }
            catch (Exception)
            {
                sqlProjects.AddValue("Properties", null);
            }
        }

        public void SetProject(clsProject item)
        {
            item.FillMemberVariables();
            SetValuesForProject(item);
            
            if (ProjectExists(item.Key))
                sqlProjects.SqlUpdateCommandObject("ItemKey=" + Strings.ConvertNullSQL(item.Key)).ExecuteNonQuery();
            else
                sqlProjects.SqlInsertCommandObject(false).ExecuteNonQuery();
        }

        public void AddProject(clsProject item)
        {
            SetValuesForProject(item);
            sqlProjects.SqlInsertCommandObject(false).ExecuteNonQuery();
        }

        public void UpdateProject(clsProject item)
        {
            SetValuesForProject(item);
            sqlProjects.SqlUpdateCommandObject("ItemKey=" + Strings.ConvertNullSQL(item.Key)).ExecuteNonQuery();
        }

        public void DeleteProject(string Key)
        {            
            sqlProjects.SqlDeleteCommandObject("ItemKey=" + Strings.ConvertNullSQL(Key), true).ExecuteNonQuery();
        }

        public bool ProjectExists(string Key)
        {
            return this.ExecuteScalar("select ItemKey from Projects WHERE ItemKey=" + Strings.ConvertNullSQL(Key)) != null;
        }

        // <<<<<<<<<<<<<<<<< TASKS >>>>>>>>>>>>>>>>>>>>

        protected void SetValuesForTask(TaskItem item)
        {
            sqlTasks.ResetData();
            sqlTasks.ResetParameters();
            sqlTasks.AddValue("ItemKey", item.Key);
            sqlTasks.AddValue("ProjectKey", ProjectKey);
            sqlTasks.AddValue("ItemText", item.Text);
            sqlTasks.AddValue("Description", item.Description);
            sqlTasks.AddValue("Visible", item.Visible);
            sqlTasks.AddValue("StartDate", item.StartDate);
            sqlTasks.AddValue("EndDate", item.EndDate);

            try
            {
                sqlTasks.AddValue("Properties", item.GetPropertiesXML());
            }
            catch (Exception)
            {
                sqlTasks.AddValue("Properties", null);
            }
            
            sqlTasks.AddValue("FormatStyle", item.FormatStyle.GetXML());

            if (item.ParentItem != null)
                sqlTasks.AddValue("ParentItemKey", item.ParentItem.Key);
            else
                sqlTasks.AddValue("ParentItemKey", null);

            sqlTasks.AddValue("PercentDone", item.PercentDone);

            // the database has different Min/Max Datetime Values,
            // so we cannot store the current values, must be set to DBNull instead
            if (item.EarliestStartDate == DateTime.MinValue)
                sqlTasks.AddValue("EarliestStartDate", DBNull.Value);
            else
                sqlTasks.AddValue("EarliestStartDate", item.EarliestStartDate);

            if (item.LatestStartDate == DateTime.MaxValue)
                sqlTasks.AddValue("LatestStartDate", DBNull.Value);
            else
                sqlTasks.AddValue("LatestStartDate", item.LatestStartDate);

            if (item.EarliestEndDate == DateTime.MinValue)
                sqlTasks.AddValue("EarliestEndDate", DBNull.Value);
            else
                sqlTasks.AddValue("EarliestEndDate", item.EarliestEndDate);

            if (item.LatestEndDate == DateTime.MaxValue)
                sqlTasks.AddValue("LatestEndDate", DBNull.Value);
            else
                sqlTasks.AddValue("LatestEndDate", item.LatestEndDate);

            sqlTasks.AddValue("StartDateRule", (int)item.StartDateRule);
            sqlTasks.AddValue("EndDateRule", (int)item.EndDateRule);
            
            sqlTasks.AddValue("IsMilestone", item.IsMilestone);
            sqlTasks.AddValue("Priority", (int)item.Priority);
            sqlTasks.AddValue("Status", (int)item.Status);

            sqlTasks.AddValue("TaskType", (int)item.TaskType);
            sqlTasks.AddValue("Estimated", item.Estimated);
            sqlTasks.AddValue("DurationType", (int)item.DurationType);
            sqlTasks.AddValue("DurationDays", item.Duration.TotalDays);

            sqlTasks.AddValue("CalendarKey", item.CalendarKey);
            sqlTasks.AddValue("ProjectCalendarCombinationMode", (int)item.ProjectCalendarCombinationMode);
            sqlTasks.AddValue("ASAP", item.ASAP);
        }

        public void SetTask(TaskItem item)
        {
            if (!TaskExists(item.Key))
                AddTask(item);
            else
                UpdateTask(item);
        }

        public void AddTask(TaskItem item)
        {
            SetValuesForTask(item);
            sqlTasks.SqlInsertCommandObject(false).ExecuteNonQuery();            
        }

        public void UpdateTask(TaskItem item)
        {
            SetValuesForTask(item);
            sqlTasks.SqlUpdateCommandObject("ItemKey=" + Strings.ConvertNullSQL(item.Key)).ExecuteNonQuery();            
        }

        public void DeleteTask(string Key)
        {            
            sqlTasks.SqlDeleteCommandObject("ItemKey=" + Strings.ConvertNullSQL(Key), true).ExecuteNonQuery();
        }

        public bool TaskExists(string Key)
        {
            return this.ExecuteScalar("select ItemKey from Tasks WHERE ItemKey=" + Strings.ConvertNullSQL(Key)) != null;
        }


        // <<<<<<<<<<<<<<<<< Groups >>>>>>>>>>>>>>>>>>>>

        protected void SetValuesForGroup(GroupItem item)
        {
            sqlGroups.ResetData();
            sqlGroups.ResetParameters();

            sqlGroups.AddValue("ItemKey", item.Key);
            sqlGroups.AddValue("ProjectKey", ProjectKey);
            sqlGroups.AddValue("ItemText", item.Text);
            sqlGroups.AddValue("Description", item.Description);
            sqlGroups.AddValue("Visible", item.Visible);
            

            try
            {
                sqlGroups.AddValue("Properties", item.GetPropertiesXML());
            }
            catch (Exception)
            {
                sqlGroups.AddValue("Properties", null);
            }

            sqlGroups.AddValue("FormatStyle", item.FormatStyle.GetXML());

            if (item.ParentItem != null)
                sqlGroups.AddValue("ParentItemKey", item.ParentItem.Key);
            else
                sqlGroups.AddValue("ParentItemKey", null);
        }

        public void SetGroup(GroupItem item)
        {
            if (!GroupExists(item.Key))
                AddGroup(item);
            else
                UpdateGroup(item);
        }

        public void AddGroup(GroupItem item)
        {
            SetValuesForGroup(item);
            sqlGroups.SqlInsertCommandObject(false).ExecuteNonQuery();
        }

        public void UpdateGroup(GroupItem item)
        {
            SetValuesForGroup(item);
            sqlGroups.SqlUpdateCommandObject("ItemKey=" + Strings.ConvertNullSQL(item.Key)).ExecuteNonQuery();
        }

        public void DeleteGroup(string Key)
        {            
            sqlGroups.SqlDeleteCommandObject("ItemKey=" + Strings.ConvertNullSQL(Key), true).ExecuteNonQuery();
        }

        public bool GroupExists(string Key)
        {            
            return this.ExecuteScalar("select ItemKey from Groups WHERE ItemKey=" + Strings.ConvertNullSQL(Key)) != null;
        }


        // <<<<<<<<<<<<<<<<< Resources >>>>>>>>>>>>>>>>>>>>

        protected void SetValuesForResource(ResourceItem item)
        {
            sqlResources.ResetData();
            sqlResources.ResetParameters();

            sqlResources.AddValue("ItemKey", item.Key);
            sqlResources.AddValue("ProjectKey", null);  // by Default, Resources are set global
            sqlResources.AddValue("ItemText", item.Text);
            sqlResources.AddValue("Description", item.Description);
            sqlResources.AddValue("Visible", item.Visible);            
            
            try
            {
                sqlResources.AddValue("Properties", item.GetPropertiesXML());
            }
            catch (Exception)
            {
                sqlResources.AddValue("Properties", null);
            }

            sqlResources.AddValue("ResourceType", (int)item.ResourceType);
            sqlResources.AddValue("Role", item.Role);
            sqlResources.AddValue("CostPerDay", item.CostPerHour);

            sqlResources.AddValue("CalendarKey", item.CalendarKey);
            sqlResources.AddValue("ProjectCalendarCombinationMode", (int)item.ProjectCalendarCombinationMode);
            sqlResources.AddValue("TaskCalendarCombinationMode", (int)item.TaskCalendarCombinationMode);
        }

        public void SetResource(ResourceItem item)
        {
            if (!ResourceExists(item.Key))
                AddResource(item);
            else
                UpdateResource(item);
        }

        public void AddResource(ResourceItem item)
        {            
            SetValuesForResource(item);
            sqlResources.SqlInsertCommandObject(false).ExecuteNonQuery();

            this.AddResourceSkillsForResource(item);
        }

        public void UpdateResource(ResourceItem item)
        {            
            SetValuesForResource(item);
            sqlResources.SqlUpdateCommandObject("ItemKey=" + Strings.ConvertNullSQL(item.Key)).ExecuteNonQuery();

            this.ClearResourceSkills(item);
            this.AddResourceSkillsForResource(item);
        }

        public void DeleteResource(string Key)
        {            
            sqlResources.SqlDeleteCommandObject("ItemKey=" + Strings.ConvertNullSQL(Key), true).ExecuteNonQuery();
        }

        public bool ResourceExists(string Key)
        {
            return this.ExecuteScalar("select ItemKey from Resources WHERE ItemKey=" + Strings.ConvertNullSQL(Key)) != null;
        }


        // <<<<<<<<<<<<<<<<< ResourceSkills >>>>>>>>>>>>>>>>>>>>        

        protected void SetValuesForResourceSkills(ResourceItem resource, SkillItem skill)
        {
            sqlResourceSkills.ResetData();
            sqlResourceSkills.ResetParameters();

            sqlResourceSkills.AddValue("ResourceKey", resource.Key);
            sqlResourceSkills.AddValue("SkillKey", skill.Key);
        }

        public void AddResourceSkill(ResourceItem resource, SkillItem skill)
        {
            SetValuesForResourceSkills(resource, skill);
            sqlResourceSkills.SqlInsertCommandObject(true).ExecuteNonQuery();
        }

        public void UpdateResourceSkill(ResourceItem resource, SkillItem skill)
        {
            SetValuesForResourceSkills(resource, skill);
            sqlResourceSkills.SqlUpdateCommandObject("(ResourceKey=" + Strings.ConvertNullSQL(resource.Key) + ") AND (SkillKey=" + Strings.ConvertNullSQL(skill.Key.ToString()) + ")").ExecuteNonQuery();
        }

        public void DeleteResourceSkill(ResourceItem resource, SkillItem skill)
        {
            sqlResourceSkills.SqlDeleteCommandObject("(ResourceKey=" + Strings.ConvertNullSQL(resource.Key) + ") AND (SkillKey=" + Strings.ConvertNullSQL(skill.Key.ToString()) + ")", true).ExecuteNonQuery();
        }

        public void ClearResourceSkills(ResourceItem resource)
        {
            sqlResourceSkills.SqlDeleteCommandObject("(ResourceKey=" + Strings.ConvertNullSQL(resource.Key) + ")", true).ExecuteNonQuery();
        }

        public void AddResourceSkillsForResource(ResourceItem resource)
        {
            foreach (SkillItem skill in resource.Skills)
                AddResourceSkill(resource, skill);
        }


        // <<<<<<<<<<<<<<<<< ResourceRelations >>>>>>>>>>>>>>>>>>>>

        protected void SetValuesForResourceRelation(TaskItem task, ResourceRelation rel)
        {
            sqlResourceRelations.ResetData();
            sqlResourceRelations.ResetParameters();

            sqlResourceRelations.AddValue("TaskKey", task.Key);
            sqlResourceRelations.AddValue("ResourceKey", rel.Resource.Key);
            sqlResourceRelations.AddValue("ProjectKey", ProjectKey);
            sqlResourceRelations.AddValue("ProposedUnit", rel.ProposedUnit);
            sqlResourceRelations.AddValue("Unit", rel.Unit);            
            sqlResourceRelations.AddValue("Responsible", rel.Responsible);
        }

        public void AddResourceRelation(TaskItem task, ResourceRelation rel)
        {
            SetValuesForResourceRelation(task, rel);
            sqlResourceRelations.SqlInsertCommandObject(false).ExecuteNonQuery();
        }

        public void UpdateResourceRelation(TaskItem task, ResourceRelation rel)
        {
            SetValuesForResourceRelation(task, rel);
            sqlResourceRelations.SqlUpdateCommandObject("(TaskKey=" + Strings.ConvertNullSQL(task.Key) + ") AND (ResourceKey=" + Strings.ConvertNullSQL(rel.Resource.Key.ToString()) + ")").ExecuteNonQuery();
        }

        public void DeleteResourceRelation(TaskItem task, ResourceRelation rel)
        {
            sqlResourceRelations.SqlDeleteCommandObject("(TaskKey=" + Strings.ConvertNullSQL(task.Key) + ") AND (ResourceKey=" + Strings.ConvertNullSQL(rel.Resource.Key.ToString()) + ")", true).ExecuteNonQuery();
        }

        public void ClearResourceRelations(TaskItem task)
        {
            sqlResourceRelations.SqlDeleteCommandObject("(TaskKey=" + Strings.ConvertNullSQL(task.Key) + ")", true).ExecuteNonQuery();
        }

        public void AddResourceRelationsForTask(TaskItem task)
        {
            foreach (ResourceRelation rel in task.Resources)
                AddResourceRelation(task, rel);
        }
        

        // <<<<<<<<<<<<<<<<< TaskRelations >>>>>>>>>>>>>>>>>>>>

        protected void SetValuesForTaskRelation(TaskItem task, TaskRelation rel)
        {
            sqlTaskRelations.ResetData();
            sqlTaskRelations.ResetParameters();

            sqlTaskRelations.AddValue("TaskKey", task.Key);
            sqlTaskRelations.AddValue("DependingTaskKey", rel.Task.Key);
            sqlTaskRelations.AddValue("ProjectKey", ProjectKey);
            sqlTaskRelations.AddValue("DelayDays", rel.DelayDays);
            sqlTaskRelations.AddValue("DelayDurationType", (int)rel.DelayDurationType);
            sqlTaskRelations.AddValue("TaskRelationMode", (int)rel.TaskRelationMode);
            sqlTaskRelations.AddValue("TaskRelationType", (int)rel.TaskRelationType);
        }

        public void AddTaskRelation(TaskItem task, TaskRelation rel)
        {
            SetValuesForTaskRelation(task, rel);
            sqlTaskRelations.SqlInsertCommandObject(false).ExecuteNonQuery();
        }

        public void UpdateTaskRelation(TaskItem task, TaskRelation rel)
        {
            SetValuesForTaskRelation(task, rel);
            sqlTaskRelations.SqlUpdateCommandObject("(TaskKey=" + Strings.ConvertNullSQL(task.Key) + ") AND (DependingTaskKey=" + Strings.ConvertNullSQL(rel.Task.Key.ToString()) + ")").ExecuteNonQuery();
        }

        public void DeleteTaskRelation(TaskItem task, TaskRelation rel)
        {
            sqlTaskRelations.SqlDeleteCommandObject("(TaskKey=" + Strings.ConvertNullSQL(task.Key) + ") AND (DependingTaskKey=" + Strings.ConvertNullSQL(rel.Task.Key.ToString()) + ")", true).ExecuteNonQuery();
        }

        public void ClearTaskRelations(TaskItem task)
        {
            sqlTaskRelations.SqlDeleteCommandObject("(TaskKey=" + Strings.ConvertNullSQL(task.Key) + ")", true).ExecuteNonQuery();
        }

        public void AddTaskRelationsForTask(TaskItem task)
        {
            foreach (TaskRelation rel in task.Dependencies)
                AddTaskRelation(task, rel);
        }


        // <<<<<<<<<<<<<<<<< Calendars >>>>>>>>>>>>>>>>>>>>
        protected void SetValuesForCalendar(KS.Gantt.Calendars.Calendar calendar)
        {
            sqlCalendars.ResetData();
            sqlCalendars.ResetParameters();

            sqlCalendars.AddValue("ItemKey", calendar.Key);
            
            sqlCalendars.AddValue("ProjectKey", null);  // by Default, Calendars are set global

            sqlCalendars.AddValue("ItemText", calendar.Text);
            sqlCalendars.AddValue("Description", calendar.Description);

            sqlCalendars.AddValue("InheritedCalendarKey", calendar.InheritedCalendarKey);

            sqlCalendars.AddValue("IsResourceCalendar", calendar.IsResourceCalendar);
            sqlCalendars.AddValue("IsTaskCalendar", calendar.IsTaskCalendar);            

            if (calendar.WorkingDays == null)
            {
                sqlCalendars.AddValue("HasWorkingHours", false);
            }
            else
            {
                sqlCalendars.AddValue("HasWorkingHours", true);
                sqlCalendars.AddValue("WorkMonday", calendar.WorkingDays.IsWorkingDay(DayOfWeek.Monday));
                sqlCalendars.AddValue("WorkTuesday", calendar.WorkingDays.IsWorkingDay(DayOfWeek.Tuesday));
                sqlCalendars.AddValue("WorkWednesday", calendar.WorkingDays.IsWorkingDay(DayOfWeek.Wednesday));
                sqlCalendars.AddValue("WorkThursday", calendar.WorkingDays.IsWorkingDay(DayOfWeek.Thursday));
                sqlCalendars.AddValue("WorkFriday", calendar.WorkingDays.IsWorkingDay(DayOfWeek.Friday));
                sqlCalendars.AddValue("WorkSaturday", calendar.WorkingDays.IsWorkingDay(DayOfWeek.Saturday));
                sqlCalendars.AddValue("WorkSunday", calendar.WorkingDays.IsWorkingDay(DayOfWeek.Sunday));
            }
        }

        public void SetCalendar(KS.Gantt.Calendars.Calendar calendar)
        {
            if (!CalendarExists(calendar.Key))
                AddCalendar(calendar);
            else
                UpdateCalendar(calendar);            
        }

        public void AddCalendar(KS.Gantt.Calendars.Calendar calendar)
        {
            SetValuesForCalendar(calendar);
            sqlCalendars.SqlInsertCommandObject(false).ExecuteNonQuery();

            AddCalendarWorkingHoursForCalendar(calendar);
            AddCalendarExceptionsForCalendar(calendar);
        }

        public void UpdateCalendar(KS.Gantt.Calendars.Calendar calendar)
        {
            SetValuesForCalendar(calendar);
            sqlCalendars.SqlUpdateCommandObject("(ItemKey=" + Strings.ConvertNullSQL(calendar.Key) + ")").ExecuteNonQuery();

            ClearCalendarWorkingHours(calendar);
            ClearCalendarExceptions(calendar);

            AddCalendarWorkingHoursForCalendar(calendar);
            AddCalendarExceptionsForCalendar(calendar);
        }

        public void DeleteCalendar(string Key)
        {
            sqlCalendars.SqlDeleteCommandObject("(ItemKey=" + Strings.ConvertNullSQL(Key) + ")", true).ExecuteNonQuery();
        }

        public bool CalendarExists(string Key)
        {
            return this.ExecuteScalar("select ItemKey from Calendars WHERE ItemKey=" + Strings.ConvertNullSQL(Key)) != null;
        }


        // <<<<<<<<<<<<<<<<< CalendarWorkingHours >>>>>>>>>>>>>>>>>>>>
        protected void SetValuesForCalendarWorkingHours(KS.Gantt.Calendars.Calendar calendar, DayOfWeek dow, KS.Gantt.Calendars.WorkingHour workinghour, DateTime ExceptionDate)
        {
            sqlCalendarWorkingHours.ResetData();
            sqlCalendarWorkingHours.ResetParameters();

            sqlCalendarWorkingHours.AddValue("CalendarKey", calendar.Key);

            sqlCalendarWorkingHours.AddValue("DayOfWeek", (int)dow);
            sqlCalendarWorkingHours.AddValue("StartHour", workinghour.StartHour);
            sqlCalendarWorkingHours.AddValue("EndHour", workinghour.EndHour);

            if (ExceptionDate > DateTime.MinValue)
                sqlCalendarWorkingHours.AddValue("ExceptionDate", ExceptionDate.Date);
            else
                sqlCalendarWorkingHours.AddValue("ExceptionDate", null);
        }

        public void AddCalendarWorkingHour(KS.Gantt.Calendars.Calendar calendar, DayOfWeek dow, KS.Gantt.Calendars.WorkingHour workinghour, DateTime ExceptionDate)
        {
            SetValuesForCalendarWorkingHours(calendar, dow, workinghour, ExceptionDate);
            sqlCalendarWorkingHours.SqlInsertCommandObject(true).ExecuteNonQuery();
        }

        protected string CalendarWorkingHoursSQLWhere(KS.Gantt.Calendars.Calendar calendar, DayOfWeek dow, DateTime ExceptionDate)
        {
            string sqlWhere = "(CalendarKey=" + Strings.ConvertNullSQL(calendar.Key) + ")";
            sqlWhere += " AND (DayOfWeek=" + ((int)dow).ToString() + ")";

            if (ExceptionDate > DateTime.MinValue)
                sqlWhere += " AND (ExceptionDate=" + Strings.SQLDate(ExceptionDate, true) + ")";
            else
                sqlWhere += " AND (ExceptionDate=NULL)";

            return sqlWhere;
        }

        public void UpdateCalendarWorkingHour(KS.Gantt.Calendars.Calendar calendar, DayOfWeek dow, KS.Gantt.Calendars.WorkingHour workinghour, DateTime ExceptionDate)
        {
            SetValuesForCalendarWorkingHours(calendar, dow, workinghour, ExceptionDate);
            sqlCalendarWorkingHours.SqlUpdateCommandObject(CalendarWorkingHoursSQLWhere(calendar, dow, ExceptionDate)).ExecuteNonQuery();
        }

        public void DeleteCalendarWorkingHour(KS.Gantt.Calendars.Calendar calendar, DayOfWeek dow, DateTime ExceptionDate)
        {
            sqlCalendarWorkingHours.SqlDeleteCommandObject(CalendarWorkingHoursSQLWhere(calendar, dow, ExceptionDate), true).ExecuteNonQuery();
        }

        public void ClearCalendarWorkingHours(KS.Gantt.Calendars.Calendar calendar)
        {            
            sqlCalendarWorkingHours.SqlDeleteCommandObject("CalendarKey=" + Strings.ConvertNullSQL(calendar.Key), true).ExecuteNonQuery();
        }

        public void AddCalendarWorkingHoursForCalendar(KS.Gantt.Calendars.Calendar calendar)
        {            
            // for all days of week
            if (calendar.WorkingDays != null)
            {
                for (int i = 0; i < 7; i++)
                {
                    foreach (KS.Gantt.Calendars.WorkingHour workinghour in calendar.WorkingDays.WorkingHours((DayOfWeek)i))
                    {
                        this.AddCalendarWorkingHour(calendar, (DayOfWeek)i, workinghour, DateTime.MinValue);
                    }
                }
            }
        }

        // <<<<<<<<<<<<<<<<< CalendarExceptions >>>>>>>>>>>>>>>>>>>>
        protected void SetValuesForCalendarExceptions(KS.Gantt.Calendars.Calendar calendar, DateTime ExceptionDate)
        {
            sqlCalendarExceptions.ResetData();
            sqlCalendarExceptions.ResetParameters();

            sqlCalendarExceptions.AddValue("CalendarKey", calendar.Key);                        

            if (ExceptionDate > DateTime.MinValue)
                sqlCalendarExceptions.AddValue("ExceptionDate", ExceptionDate.Date);
            else
                sqlCalendarExceptions.AddValue("ExceptionDate", DBNull.Value);

            sqlCalendarExceptions.AddValue("IsWorkingDay", calendar.FindException(ExceptionDate).IsWorkingDay);
        }

        public void AddCalendarException(KS.Gantt.Calendars.Calendar calendar, DateTime ExceptionDate)
        {
            SetValuesForCalendarExceptions(calendar, ExceptionDate);
            sqlCalendarExceptions.SqlInsertCommandObject(false).ExecuteNonQuery();
        }

        protected string CalendarExceptionsSQLWhere(KS.Gantt.Calendars.Calendar calendar, DateTime ExceptionDate)
        {
            string sqlWhere = "(CalendarKey=" + Strings.ConvertNullSQL(calendar.Key) + ")";            

            if (ExceptionDate > DateTime.MinValue)
                sqlWhere += " AND (ExceptionDate=" + Strings.SQLDate(ExceptionDate, true) + ")";
            else
                sqlWhere += " AND (ExceptionDate=NULL)";

            return sqlWhere;
        }

        public void UpdateCalendarException(KS.Gantt.Calendars.Calendar calendar, DateTime ExceptionDate)
        {
            SetValuesForCalendarExceptions(calendar, ExceptionDate);
            sqlCalendarExceptions.SqlUpdateCommandObject(CalendarExceptionsSQLWhere(calendar, ExceptionDate)).ExecuteNonQuery();
        }

        public void DeleteCalendarException(KS.Gantt.Calendars.Calendar calendar, DateTime ExceptionDate)
        {
            sqlCalendarExceptions.SqlDeleteCommandObject(CalendarExceptionsSQLWhere(calendar, ExceptionDate), true).ExecuteNonQuery();
        }

        public void ClearCalendarExceptions(KS.Gantt.Calendars.Calendar calendar)
        {            
            sqlCalendarExceptions.SqlDeleteCommandObject("CalendarKey=" + Strings.ConvertNullSQL(calendar.Key), true).ExecuteNonQuery();
        }

        public void AddCalendarExceptionsForCalendar(KS.Gantt.Calendars.Calendar calendar)
        {            
            if (calendar.Exceptions != null)
            {
                foreach (KS.Gantt.Calendars.CalendarException calendarexception in calendar.Exceptions)
                {
                    this.AddCalendarException(calendar, calendarexception.Date);

                    if (calendarexception.WorkingHours != null)
                    {
                        foreach (KS.Gantt.Calendars.WorkingHour wh in calendarexception.WorkingHours)
                        {
                            AddCalendarWorkingHour(calendar, calendarexception.Date.DayOfWeek, wh, calendarexception.Date);
                        }
                    }
                }
            }
        }        

        // <<<<<<<<<<<<<<<<< SKILLS >>>>>>>>>>>>>>>>>>>>

        protected void SetValuesForSkill(SkillItem item)
        {
            sqlSkills.ResetData();
            sqlSkills.ResetParameters();
            sqlSkills.AddValue("ItemKey", item.Key);
            sqlSkills.AddValue("ProjectKey", null);  // by Default, Skills are set global            
            sqlSkills.AddValue("ItemText", item.Text);
            sqlSkills.AddValue("Description", item.Description);
            sqlSkills.AddValue("Productivity", item.Productivity);
        }

        public void SetSkill(SkillItem item)
        {
            SetValuesForSkill(item);

            if (SkillExists(item.Key))
                sqlSkills.SqlUpdateCommandObject("ItemKey=" + Strings.ConvertNullSQL(item.Key)).ExecuteNonQuery();
            else
                sqlSkills.SqlInsertCommandObject(false).ExecuteNonQuery();
        }

        public void AddSkill(SkillItem item)
        {
            SetValuesForSkill(item);
            sqlSkills.SqlInsertCommandObject(false).ExecuteNonQuery();
        }

        public void UpdateSkill(SkillItem item)
        {
            SetValuesForSkill(item);
            sqlSkills.SqlUpdateCommandObject("ItemKey=" + Strings.ConvertNullSQL(item.Key)).ExecuteNonQuery();
        }

        public void DeleteSkill(string Key)
        {
            sqlSkills.SqlDeleteCommandObject("ItemKey=" + Strings.ConvertNullSQL(Key), true).ExecuteNonQuery();
        }

        public bool SkillExists(string Key)
        {
            return this.ExecuteScalar("select ItemKey from Skills WHERE ItemKey=" + Strings.ConvertNullSQL(Key)) != null;
        }

        // <<<<<<<<<<<<<<<<< Works >>>>>>>>>>>>>>>>>>>>

        protected void SetValuesForWork(WorkItem work, TaskItem task)
        {
            sqlWorks.ResetData();
            sqlWorks.ResetParameters();
            sqlWorks.AddValue("ItemKey", work.Key);
            sqlWorks.AddValue("TaskKey", task.Key);
            sqlWorks.AddValue("ProjectKey", ProjectKey);
            sqlWorks.AddValue("ItemText", work.Text);
            sqlWorks.AddValue("Description", work.Description);

            sqlWorks.AddValue("ProposedWorkHours", work.ProposedWorkHours);
            sqlWorks.AddValue("WorkHours", work.WorkHours);            

            sqlWorks.AddValue("SkillKey", work.SkillKey);
            sqlWorks.AddValue("MaxEntities", work.MaxEntities);
        }

        public void SetWork(WorkItem work, TaskItem task)
        {
            if (!WorkExists(work.Key))
                AddWork(work, task);
            else
                UpdateWork(work, task);
        }

        public void AddWork(WorkItem work, TaskItem task)
        {
            SetValuesForWork(work, task);
            sqlWorks.SqlInsertCommandObject(false).ExecuteNonQuery();

            foreach (ResourceItem resource in work.PreferredResources)
                AddWorkPreferredResource(work, resource);
        }

        public void UpdateWork(WorkItem work, TaskItem task)
        {
            SetValuesForWork(work, task);
            sqlWorks.SqlUpdateCommandObject("ItemKey=" + Strings.ConvertNullSQL(work.Key)).ExecuteNonQuery();

            ClearWorkPreferredResources(work);
            foreach (ResourceItem resource in work.PreferredResources)
                AddWorkPreferredResource(work, resource);
        }

        public void DeleteWork(WorkItem item)
        {            
            sqlWorks.SqlDeleteCommandObject("ItemKey=" + Strings.ConvertNullSQL(item.Key), true).ExecuteNonQuery();
        }

        public void ClearWorks(TaskItem task)
        {
            sqlWorks.SqlDeleteCommandObject("TaskKey=" + Strings.ConvertNullSQL(task.Key), true).ExecuteNonQuery();
        }

        public bool WorkExists(string Key)
        {
            return this.ExecuteScalar("select ItemKey from Works WHERE ItemKey=" + Strings.ConvertNullSQL(Key)) != null;
        }

        public void AddWorksForTask(TaskItem task)
        {
            foreach (WorkItem work in task.Works)                            
                AddWork(work, task);            
        }

        public void SetWorksForTask(TaskItem task)
        {
            foreach (WorkItem work in task.Works)
            {
                if (!WorkExists(work.Key))
                    AddWork(work, task);
                else
                    UpdateWork(work, task);
            }
        }        

        // <<<<<<<<<<<<<<<<< WorkPreferredResources >>>>>>>>>>>>>>>>>>>>

        protected void SetValuesForWorkPreferredResources(WorkItem work, ResourceItem resource)
        {
            sqlWorkPreferredResources.ResetData();
            sqlWorkPreferredResources.ResetParameters();
            sqlWorkPreferredResources.AddValue("WorkKey", work.Key);
            sqlWorkPreferredResources.AddValue("ResourceKey", resource.Key);            
        }

        public void AddWorkPreferredResource(WorkItem work, ResourceItem resource)
        {
            SetValuesForWorkPreferredResources(work, resource);
            sqlWorkPreferredResources.SqlInsertCommandObject(true).ExecuteNonQuery();
        }

        public void ClearWorkPreferredResources(WorkItem work)
        {
            sqlWorkPreferredResources.SqlDeleteCommandObject("WorkKey=" + Strings.ConvertNullSQL(work.Key), true).ExecuteNonQuery();
        }        


        // <<<<<<<<<<<<<<<<< ResourceAllocations >>>>>>>>>>>>>>>>>>>>

        protected void SetValuesForResourceAllocations(ResourceAllocation item, ResourceItem resource)
        {
            sqlResourceAllocations.ResetData();
            sqlResourceAllocations.ResetParameters();
            
            sqlResourceAllocations.AddValue("ResourceKey", resource.Key);
            sqlResourceAllocations.AddValue("TaskKey", item.TaskKey);
            sqlResourceAllocations.AddValue("TaskCalendarKey", item.TaskCalendarKey);
            sqlResourceAllocations.AddValue("TaskCalendarCombinationMode", (int)item.TaskCalendarCombinationMode);
            sqlResourceAllocations.AddValue("ProjectKey", ProjectKey);
            sqlResourceAllocations.AddValue("StartDate", item.StartDate);
            sqlResourceAllocations.AddValue("EndDate", item.EndDate);
            sqlResourceAllocations.AddValue("Unit", item.Unit);
            sqlResourceAllocations.AddValue("TaskText", item.TaskText);
            sqlResourceAllocations.AddValue("ProjectText", item.ProjectText);
        }

        public void AddResourceAllocation(ResourceAllocation item, ResourceItem resource)
        {
            SetValuesForResourceAllocations(item, resource);
            sqlResourceAllocations.SqlInsertCommandObject(true).ExecuteNonQuery();
        }        
        
        public void ClearResourceAllocationsForResource(ResourceItem resource)
        {
            sqlResourceAllocations.SqlDeleteCommandObject("ResourceKey=" + Strings.ConvertNullSQL(resource.Key), true).ExecuteNonQuery();
        }

        public void ClearResourceAllocationsForProject(string projectKey)
        {
            sqlResourceAllocations.SqlDeleteCommandObject("ProjectKey=" + Strings.ConvertNullSQL(projectKey), true).ExecuteNonQuery();
        }

        public void AddResourceAllocationsForResource(ResourceItem item)
        {
            foreach (ResourceAllocation ra in item.ResourceAllocations)
            {
                if (ra.ProjectKey == ProjectKey)
                    AddResourceAllocation(ra, item);
            }
        }

        public void SetResourceAllocationsForResource(ResourceItem item)
        {
            ClearResourceAllocationsForResource(item);
            AddResourceAllocationsForResource(item);
        }

        // ********* Transaction Management *************

        private void SetTransaction()
        {
            sqlProjects.SQLTrans = this.Transaction;
            sqlTasks.SQLTrans = this.Transaction;
            sqlResources.SQLTrans = this.Transaction;
            sqlGroups.SQLTrans = this.Transaction;
            sqlResourceRelations.SQLTrans = this.Transaction;
            sqlTaskRelations.SQLTrans = this.Transaction;
            sqlResourceSkills.SQLTrans = this.Transaction;
            sqlCalendars.SQLTrans = this.Transaction;
            sqlCalendarWorkingHours.SQLTrans = this.Transaction;
            sqlCalendarExceptions.SQLTrans = this.Transaction;
            sqlSkills.SQLTrans = this.Transaction;
            sqlWorks.SQLTrans = this.Transaction;
            sqlWorkPreferredResources.SQLTrans = this.Transaction;
            sqlResourceAllocations.SQLTrans = this.Transaction;
        }

        public override void BeginTransaction()
        {
            try
            {
                base.BeginTransaction();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                SetTransaction();
            }
        }

        public override void CommitTransaction()
        {
            try
            {
                base.CommitTransaction();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                SetTransaction();
            }
        }

        public override void RollbackTransaction()
        {
            try
            {
                base.RollbackTransaction();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                SetTransaction();
            }
        }

        // ************** Meta Functions ******************        

        /// <summary>
        /// Overwrites the database with current values
        /// </summary>
        /// <param name="gantt"></param>
        public void WriteDB(Gantt gantt)
        {
            if (!IsConnected)
                Connect();

            gantt.UpdateResourceAllocations();
            ProjectKey = gantt.Project.Key;            

            try
            {
                this.BeginTransaction();

                // Clear everything..
                this.Clear(ProjectKey);

                SetProject(gantt.Project);

                // Skills
                foreach (SkillItem item in gantt.Skills)
                    this.SetSkill(item);

                // calendars
                foreach (KS.Gantt.Calendars.Calendar item in gantt.CalendarList)
                    this.SetCalendar(item);

                // Resources
                foreach (ResourceItem item in gantt.Resources)
                    this.SetResource(item);

                // Groups
                foreach (GroupItem item in gantt.Groups)
                    this.SetGroup(item);

                // Tasks
                foreach (TaskItem item in gantt.Tasks)
                    this.SetTask(item);

                foreach (TaskItem item in gantt.Tasks)
                {
                    this.AddWorksForTask(item);
                    this.AddTaskRelationsForTask(item);
                    this.AddResourceRelationsForTask(item);
                }

                // ResourceAllocations
                this.ClearResourceAllocationsForProject(ProjectKey);
                foreach (ResourceItem resource in gantt.Resources)
                {
                    AddResourceAllocationsForResource(resource);
                }

                this.CommitTransaction();
                gantt.SetHistoryModified();
                gantt.ResetModified();
            }
            catch (Exception ex)
            {
                try
                {
                    this.RollbackTransaction();
                }
                catch
                {
                }

                throw ex;
            }            
        }

        /// <summary>
        /// Writes only changes to the database
        /// </summary>
        /// <param name="gantt"></param>
        public void UpdateDB(Gantt gantt)
        {
            if (!IsConnected)
                Connect();

            gantt.UpdateResourceAllocations();
            ProjectKey = gantt.Project.Key;

            try
            {
                this.BeginTransaction();              

                //if (gantt.Project.Modified)
                SetProject(gantt.Project);                

                // Skills
                foreach (string key in gantt.Skills.ItemKeysRemoved)
                {
                    this.DeleteSkill(key);
                }

                foreach (string key in gantt.Skills.ItemKeysAdded)
                {
                    SkillItem item = gantt.Skills.FindItemByKey(key);
                    if (item != null)
                        this.SetSkill(item);
                }

                foreach (string key in gantt.Skills.ItemKeysUpdated)
                {
                    SkillItem item = gantt.Skills.FindItemByKey(key);
                    if (item != null)                
                        this.SetSkill(item);
                }                

                // calendars
                foreach (string key in gantt.CalendarList.ItemKeysRemoved)
                {
                    this.DeleteCalendar(key);
                }

                foreach (string key in gantt.CalendarList.ItemKeysAdded)
                {
                    KS.Gantt.Calendars.Calendar item = gantt.CalendarList.FindItemByKey(key);
                    if (item != null)
                        this.SetCalendar(item); // Set, because of Default-Calendar
                }

                foreach (string key in gantt.CalendarList.ItemKeysUpdated)
                {
                    KS.Gantt.Calendars.Calendar item = gantt.CalendarList.FindItemByKey(key);
                    if (item != null)
                        this.SetCalendar(item); // Set, because of Default-Calendar
                }                


                // Resources
                foreach (string key in gantt.Resources.ItemKeysRemoved)
                {
                    this.DeleteResource(key);
                }

                foreach (string key in gantt.Resources.ItemKeysAdded)
                {
                    ResourceItem item = gantt.Resources.FindItemByKey(key);
                    if (item != null)
                        this.SetResource(item);
                }

                foreach (string key in gantt.Resources.ItemKeysUpdated)
                {
                    ResourceItem item = gantt.Resources.FindItemByKey(key);
                    if (item != null)
                        this.SetResource(item);
                }                

                // Groups
                foreach (string key in gantt.Groups.ItemKeysRemoved)
                {
                    this.DeleteGroup(key);
                }

                foreach (string key in gantt.Groups.ItemKeysAdded)
                {
                    GroupItem item = gantt.Groups.FindItemByKey(key);
                    if (item != null)
                        this.SetGroup(item);
                }

                foreach (string key in gantt.Groups.ItemKeysUpdated)
                {
                    GroupItem item = gantt.Groups.FindItemByKey(key);
                    if (item != null)
                        this.SetGroup(item);
                }                

                // Tasks
                foreach (string key in gantt.Tasks.ItemKeysRemoved)
                {
                    this.DeleteTask(key);
                }

                foreach (string key in gantt.Tasks.ItemKeysAdded)
                {
                    TaskItem item = gantt.Tasks.FindItemByKey(key);
                    if (item != null)
                        this.SetTask(item);
                }

                foreach (string key in gantt.Tasks.ItemKeysUpdated)
                {
                    TaskItem item = gantt.Tasks.FindItemByKey(key);
                    if (item != null)
                        this.SetTask(item);
                }                


                // TaskRelations / ResourceRelations / Works
                foreach (string key in gantt.Tasks.ItemKeysAdded)
                {
                    TaskItem item = gantt.Tasks.FindItemByKey(key);
                    if (item != null)
                    {
                        //this.SetWorksForTask(item);

                        this.ClearWorks(item);
                        this.ClearTaskRelations(item);  // dispensable ? no, not after Solver
                        this.ClearResourceRelations(item);  // dispensable ? no, not after Solver

                        this.AddWorksForTask(item);
                        this.AddTaskRelationsForTask(item);
                        this.AddResourceRelationsForTask(item);
                    }
                }

                foreach (string key in gantt.Tasks.ItemKeysUpdated)
                {
                    TaskItem item = gantt.Tasks.FindItemByKey(key);
                    if (item != null)
                    {                        
                        this.ClearWorks(item);
                        this.ClearTaskRelations(item);
                        this.ClearResourceRelations(item);

                        this.AddWorksForTask(item);
                        this.AddTaskRelationsForTask(item);
                        this.AddResourceRelationsForTask(item);
                    }
                }

                // ResourceAllocations            
                this.ClearResourceAllocationsForProject(ProjectKey);
                foreach (ResourceItem resource in gantt.Resources)
                {
                    AddResourceAllocationsForResource(resource);                    
                }

                this.CommitTransaction();

                //gantt.SetHistoryModified();
                gantt.HistoryClear();
                gantt.ResetModified();
            }
            catch (Exception ex)
            {
                try
                {
                    this.RollbackTransaction();
                }
                catch
                {
                }

                throw ex;
            }
        }


        /// <summary>
        /// Reads global data from the database 
        /// as global Calendars, Resources and Skills
        /// </summary>
        /// <param name="gantt"></param>
        public void ReadDBGlobal(Gantt gantt)
        {
            ReadDB(gantt, "");
        }

        /// <summary>
        /// Reads a project from the database
        /// </summary>
        /// <param name="gantt"></param>
        /// <param name="projectKey"></param>
        public void ReadDB(Gantt gantt, string projectKey)
        {
            ProjectKey = projectKey;

            if (!IsConnected)
                Connect();

            gantt.SuspendItemLayout();

            string strWhereProjectKey = " WHERE (ProjectKey=" + Strings.ConvertNullSQL(ProjectKey) + ")";
            string strWhereProjectKeyOrNull = "";

            if (String.IsNullOrEmpty(projectKey))
                strWhereProjectKeyOrNull = " WHERE (ProjectKey IS NULL)";
            else
                strWhereProjectKeyOrNull = " WHERE ((ProjectKey IS NULL) Or (ProjectKey=" + Strings.ConvertNullSQL(ProjectKey) + "))";

            System.Data.SqlClient.SqlDataReader dr = null;

            // Temporary Lists
            clsProject project = null;
            List<KS.Gantt.Calendars.Calendar> calendars = null;
            List<SkillItem> skills = null;
            List<ResourceItem> resources = null;
            List<GroupItem> groups = null;
            List<TaskItem> tasks = null;

            Granularities granularity = Granularities.Minute;

            try
            {
                this.BeginTransaction();

                if (!String.IsNullOrEmpty(projectKey))
                {
                    // Project
                    dr = this.ExecuteSQL("select * from Projects where ItemKey=" + Strings.ConvertNullSQL(ProjectKey));
                    if (dr != null && dr.Read())
                    {
                        project = new clsProject();

                        project.Key = Strings.GetSafeString(dr["ItemKey"]);
                        project.Text = Strings.GetSafeString(dr["ItemText"]);
                        project.Description = Strings.GetSafeString(dr["Description"]);
                        project.SetPropertiesXML(Strings.GetSafeString(dr["Properties"]));

                        granularity = (Granularities)Strings.GetSafeInteger(dr["Granularity"], 1);
                        project.Granularity = granularity;
                        project.MinMaxLinesVisible = Strings.GetSafeBool(dr["MinMaxLinesVisible"]);
                        project.MinMaxLineStyle = (LineStyle)new LineStyle().SetXML(Strings.GetSafeString(dr["MinMaxLineStyle"]));
                        project.TodayLineVisible = Strings.GetSafeBool(dr["TodayLineVisible"]);
                        project.TodayLineStyle = (LineStyle)new LineStyle().SetXML(Strings.GetSafeString(dr["TodayLineStyle"]));

                        project.ShowWeekends = Strings.GetSafeBool(dr["ShowWeekends"]);
                        project.WeekendFormatStyle = (HeaderFormatStyle)new HeaderFormatStyle().SetXML(Strings.GetSafeString(dr["WeekendFormatStyle"]));

                        project.ShowWorkingHours = Strings.GetSafeBool(dr["ShowWorkingHours"]);
                        project.ShowWeeks = Strings.GetSafeBool(dr["ShowWeeks"]);
                        project.GridLines = Strings.GetSafeBool(dr["GridLines"]);

                        project.GridLineStyle = (LineStyle)new LineStyle().SetXML(Strings.GetSafeString(dr["GridLineStyle"]));
                        project.DefaultBarFormatStyle = (BarFormatStyle)new BarFormatStyle().SetXML(Strings.GetSafeString(dr["DefaultBarFormatStyle"]));
                        project.DefaultGroupLineStyle = (BarFormatStyle)new BarFormatStyle().SetXML(Strings.GetSafeString(dr["DefaultGroupLineStyle"]));
                        project.ResourceBarStyle = (BarFormatStyle)new BarFormatStyle().SetXML(Strings.GetSafeString(dr["ResourceBarStyle"]));
                        project.RelationLineStyle = (LineStyle)new LineStyle().SetXML(Strings.GetSafeString(dr["RelationLineStyle"]));

                        project.RelationLineArrowWidth = Strings.GetSafeInteger(dr["RelationLineArrowWidth"], 2);
                        project.ShowCriticalPath = Strings.GetSafeBool(dr["ShowCriticalPath"]);
                        project.CriticalPathLineStyle = (LineStyle)new LineStyle().SetXML(Strings.GetSafeString(dr["CriticalPathLineStyle"]));
                        project.BarTooltips = (Gantt.TooltipDisplayOptions)Strings.GetSafeInteger(dr["BarTooltips"], 0);
                        project.ItemTextDisplay = (Gantt.ItemTextDisplayModes)Strings.GetSafeInteger(dr["ItemTextDisplay"], 0);
                        project.BarStyle = (Gantt.BarStyles)Strings.GetSafeInteger(dr["BarStyle"], 0);

                        project.BarHeight = Strings.GetSafeInteger(dr["BarHeight"], 8);
                        project.RowHeight = Strings.GetSafeInteger(dr["RowHeight"], 12);
                        project.PercentBarDeflate = new System.Drawing.Size(Strings.GetSafeInteger(dr["PercentBarDeflateX"], 0), Strings.GetSafeInteger(dr["PercentBarDeflateY"], 0));
                        project.RandomBarColors = Strings.GetSafeBool(dr["RandomBarColors"]);
                        project.ResourceBarStyleMode = (Gantt.ResourceBarStyleModes)Strings.GetSafeInteger(dr["ResourceBarStyleMode"], 0);
                        project.ResourceViewFilter = (Gantt.ResourceViewFilters)Strings.GetSafeInteger(dr["ResourceViewFilter"], 0);

                        project.ResourceStandardColor = XmlHelpers.DeserializeColor(Strings.GetSafeString(dr["ResourceStandardColor"]));
                        project.ResourceOverloadColor = XmlHelpers.DeserializeColor(Strings.GetSafeString(dr["ResourceOverloadColor"]));
                        project.ResourceUnderloadColor = XmlHelpers.DeserializeColor(Strings.GetSafeString(dr["ResourceUnderloadColor"]));

                        project.ShowIcons = Strings.GetSafeBool(dr["ShowIcons"]);
                        project.ShowPriorityIcons = Strings.GetSafeBool(dr["ShowPriorityIcons"]);
                        project.ShowStatusIcons = Strings.GetSafeBool(dr["ShowStatusIcons"]);
                        project.ShowOverDueIcon = Strings.GetSafeBool(dr["ShowOverDueIcon"]);
                        project.ShowHappinessIcon = Strings.GetSafeBool(dr["ShowHappinessIcon"]);
                        project.ShowAlignmentIcon = Strings.GetSafeBool(dr["ShowAlignmentIcon"]);
                        project.ShowCustomIcons = Strings.GetSafeBool(dr["ShowCustomIcons"]);

                        project.StartDate = Strings.GetSafeDateTime(dr["StartDate"], DateTime.Now.AddDays(-30));
                        project.EndDate = Strings.GetSafeDateTime(dr["EndDate"], DateTime.Now.AddDays(30));

                        project.DefaultTaskType = (TaskItem.TaskTypes)Strings.GetSafeInteger(dr["DefaultTaskType"], 0);
                        project.DefaultDurationType = (TaskItem.DurationTypes)Strings.GetSafeInteger(dr["DefaultDurationType"], 0);
                        project.NewTaskCount = Strings.GetSafeInteger(dr["NewTaskCount"], 0);
                        project.ColorCyclerCurrentIndex = Strings.GetSafeInteger(dr["ColorCyclerCurrentIndex"], 0);
                        project.ScrollOffsetY = Strings.GetSafeInteger(dr["ScrollOffsetY"], 0);
                        
                        project.ResourceBarColorTolerance = Strings.GetSafeDouble(dr["ResourceBarColorTolerance"], 0.1);
                    }                    

                    if (dr != null)
                        dr.Close();
                }

                // ** Calendars **
                calendars = new List<KS.Gantt.Calendars.Calendar>();

                dr = this.ExecuteSQL("select * from Calendars" + strWhereProjectKeyOrNull);
                while (dr.Read())
                {
                    KS.Gantt.Calendars.Calendar calendar = new KS.Gantt.Calendars.Calendar();

                    calendar.Key = Strings.GetSafeString(dr["ItemKey"]);
                    calendar.Text = Strings.GetSafeString(dr["ItemText"]);
                    calendar.Description = Strings.GetSafeString(dr["Description"]);

                    calendar.InheritedCalendarKey = Strings.GetSafeString(dr["InheritedCalendarKey"]);
                    calendar.IsResourceCalendar = Strings.GetSafeBool(dr["IsResourceCalendar"]);
                    calendar.IsTaskCalendar = Strings.GetSafeBool(dr["IsTaskCalendar"]);

                    if (Strings.GetSafeBool(dr["HasWorkingHours"]))
                    {
                        calendar.SetWorkingDay(DayOfWeek.Monday, Strings.GetSafeBool(dr["WorkMonday"]));
                        calendar.SetWorkingDay(DayOfWeek.Tuesday, Strings.GetSafeBool(dr["WorkTuesday"]));
                        calendar.SetWorkingDay(DayOfWeek.Wednesday, Strings.GetSafeBool(dr["WorkWednesday"]));
                        calendar.SetWorkingDay(DayOfWeek.Thursday, Strings.GetSafeBool(dr["WorkThursday"]));
                        calendar.SetWorkingDay(DayOfWeek.Friday, Strings.GetSafeBool(dr["WorkFriday"]));
                        calendar.SetWorkingDay(DayOfWeek.Saturday, Strings.GetSafeBool(dr["WorkSaturday"]));
                        calendar.SetWorkingDay(DayOfWeek.Sunday, Strings.GetSafeBool(dr["WorkSunday"]));
                    }

                    calendars.Add(calendar);                    
                }
                dr.Close();


                foreach (KS.Gantt.Calendars.Calendar calendar in calendars)
                {
                    // ** CalendarExceptions **
                    dr = this.ExecuteSQL("select * from CalendarExceptions" + " WHERE CalendarKey=" + Strings.ConvertNullSQL(calendar.Key));
                    while (dr.Read())
                    {
                        DateTime exceptiondate = Strings.GetSafeDateTime(dr["ExceptionDate"], DateTime.MinValue);
                        bool isWorkingDay = Strings.GetSafeBool(dr["IsWorkingDay"]);
                        calendar.AddException(new KS.Gantt.Calendars.CalendarException(exceptiondate, isWorkingDay));
                    }
                    dr.Close();

                    // ** CalendarWorkingHours **
                    dr = this.ExecuteSQL("select * from CalendarWorkingHours" + " WHERE CalendarKey=" + Strings.ConvertNullSQL(calendar.Key));
                    while (dr.Read())
                    {
                        DayOfWeek dow = (DayOfWeek)Strings.GetSafeInteger(dr["DayOfWeek"], 0);
                        double StartHour = Strings.GetSafeDouble(dr["StartHour"], 0.0);
                        double EndHour = Strings.GetSafeDouble(dr["EndHour"], 0.0);

                        DateTime ExceptionDate = Strings.GetSafeDateTime(dr["ExceptionDate"], DateTime.MinValue);

                        if (ExceptionDate == DateTime.MinValue)
                        {
                            calendar.AddWorkingHour(dow, new KS.Gantt.Calendars.WorkingHour(StartHour, EndHour));
                        }
                        else
                        {
                            KS.Gantt.Calendars.CalendarException calendarexception = calendar.Exceptions.FindException(ExceptionDate.Date);
                            if (calendarexception != null)
                            {                                
                                calendarexception.AddWorkingHour(new KS.Gantt.Calendars.WorkingHour(StartHour, EndHour));
                            }
                        }
                    }
                    dr.Close();
                }


                // ** Skills **
                skills = new List<SkillItem>();

                dr = this.ExecuteSQL("select * from Skills" + strWhereProjectKeyOrNull);
                while (dr.Read())
                {
                    SkillItem skill = new SkillItem();

                    skill.Key = Strings.GetSafeString(dr["ItemKey"]);
                    skill.Text = Strings.GetSafeString(dr["ItemText"]);
                    skill.Description = Strings.GetSafeString(dr["Description"]);
                    skill.Productivity = Strings.GetSafeDouble(dr["Productivity"], 0.0);

                    skills.Add(skill);
                }
                dr.Close();

                // ** Resources **
                resources = new List<ResourceItem>();

                dr = this.ExecuteSQL("select * from Resources" + strWhereProjectKeyOrNull);
                while (dr.Read())
                {
                    ResourceItem resource = new ResourceItem();

                    resource.Key = Strings.GetSafeString(dr["ItemKey"]);
                    resource.Role = Strings.GetSafeString(dr["Role"]);
                    resource.Text = Strings.GetSafeString(dr["ItemText"]);
                    resource.Description = Strings.GetSafeString(dr["Description"]);
                    resource.CostPerHour = Strings.GetSafeDouble(dr["CostPerDay"], 0);

                    resource.CalendarKey = Strings.GetSafeString(dr["CalendarKey"]);
                    resource.ProjectCalendarCombinationMode = (KS.Gantt.Calendars.CalendarCombinationModes)Strings.GetSafeInteger(dr["ProjectCalendarCombinationMode"], 0);
                    resource.TaskCalendarCombinationMode = (KS.Gantt.Calendars.CalendarCombinationModes)Strings.GetSafeInteger(dr["TaskCalendarCombinationMode"], 0);

                    try
                    {
                        resource.ResourceType = (ResourceTypes)Strings.GetSafeInteger(dr["ResourceType"], 0);
                    }
                    catch { }

                    resource.Visible = Strings.GetSafeBool(dr["Visible"]);

                    try
                    {
                        resource.SetPropertiesXML(Strings.GetSafeString(dr["Properties"]));
                    }
                    catch { }
                    
                    resources.Add(resource);
                }
                dr.Close();


                foreach (ResourceItem resource in resources)
                {
                    dr = this.ExecuteSQL("select * from ResourceSkills WHERE ResourceKey=" + Strings.ConvertNullSQL(resource.Key));
                    while (dr.Read())
                    {                        
                        string SkillKey = Strings.GetSafeString(dr["SkillKey"]);
                        resource.AddSkillKey(SkillKey);
                    }
                    dr.Close();
                }

                if (!String.IsNullOrEmpty(projectKey))
                {                    
                    // ** Groups **
                    groups = new List<GroupItem>();

                    dr = this.ExecuteSQL("select * from Groups" + strWhereProjectKey);
                    while (dr.Read())
                    {
                        GroupItem group = new GroupItem(granularity);

                        group.Key = Strings.GetSafeString(dr["ItemKey"]);
                        group.Text = Strings.GetSafeString(dr["ItemText"]);
                        group.Description = Strings.GetSafeString(dr["Description"]);
                        group.Visible = Strings.GetSafeBool(dr["Visible"]);

                        try
                        {
                            group.SetPropertiesXML(Strings.GetSafeString(dr["Properties"]));
                        }
                        catch { }

                        try
                        {
                            group.FormatStyle = (BarFormatStyle)group.FormatStyle.SetXML(Strings.GetSafeString(dr["FormatStyle"]));
                        }
                        catch { }

                        group.ParentItemKey = Strings.GetSafeString(dr["ParentItemKey"]);
                        
                        groups.Add(group);
                    }
                    dr.Close();


                    // ** Tasks **
                    tasks = new List<TaskItem>();

                    dr = this.ExecuteSQL("select * from Tasks" + strWhereProjectKey);
                    while (dr.Read())
                    {
                        TaskItem task = new TaskItem(granularity);

                        task.Key = Strings.GetSafeString(dr["ItemKey"]);
                        task.Text = Strings.GetSafeString(dr["ItemText"]);
                        task.Description = Strings.GetSafeString(dr["Description"]);
                        task.Visible = Strings.GetSafeBool(dr["Visible"]);

                        try
                        {
                            task.SetPropertiesXML(Strings.GetSafeString(dr["Properties"]));
                        }
                        catch { }


                        try
                        {
                            task.FormatStyle = (BarFormatStyle)task.FormatStyle.SetXML(Strings.GetSafeString(dr["FormatStyle"]));
                        }
                        catch { }

                        task.ParentItemKey = Strings.GetSafeString(dr["ParentItemKey"]);

                        task.PercentDone = Strings.GetSafeDouble(dr["PercentDone"], 0d);

                        // Set Tasktype before any dates
                        task.TaskType = (TaskItem.TaskTypes)Strings.GetSafeInteger(dr["TaskType"], 0);

                        task.CalendarKey = Strings.GetSafeString(dr["CalendarKey"]);
                        task.ProjectCalendarCombinationMode = (KS.Gantt.Calendars.CalendarCombinationModes)Strings.GetSafeInteger(dr["ProjectCalendarCombinationMode"], 0);

                        task.ASAP = Strings.GetSafeBool(dr["ASAP"]);

                        task.BeginEdit();

                        if (dr["EarliestStartDate"] == DBNull.Value)
                            task.EarliestStartDate = DateTime.MinValue;
                        else
                            task.EarliestStartDate = Strings.GetSafeDateTime(dr["EarliestStartDate"], DateTime.MinValue);

                        if (dr["LatestStartDate"] == DBNull.Value)
                            task.LatestStartDate = DateTime.MaxValue;
                        else
                            task.LatestStartDate = Strings.GetSafeDateTime(dr["LatestStartDate"], DateTime.MaxValue);

                        if (dr["EarliestEndDate"] == DBNull.Value)
                            task.EarliestEndDate = DateTime.MinValue;
                        else
                            task.EarliestEndDate = Strings.GetSafeDateTime(dr["EarliestEndDate"], DateTime.MinValue);

                        if (dr["LatestEndDate"] == DBNull.Value)
                            task.LatestEndDate = DateTime.MaxValue;
                        else
                            task.LatestEndDate = Strings.GetSafeDateTime(dr["LatestEndDate"], DateTime.MaxValue);

                        task.StartDateRule = (DateRules)Strings.GetSafeInteger(dr["StartDateRule"], 0);
                        task.EndDateRule = (DateRules)Strings.GetSafeInteger(dr["EndDateRule"], 0);

                        task.StartDate = Strings.GetSafeDateTime(dr["StartDate"], DateTime.MaxValue);
                        task.EndDate = Strings.GetSafeDateTime(dr["EndDate"], DateTime.MaxValue);

                        task.IsMilestone = Strings.GetSafeBool(dr["IsMilestone"]);
                        task.Priority = (TaskItem.TaskPriorities)Strings.GetSafeInteger(dr["Priority"], 0);
                        task.Status = (TaskItem.TaskStates)Strings.GetSafeInteger(dr["Status"], 0);

                        if (task.TaskType != TaskItem.TaskTypes.StaticTask)
                        {
                            task.Estimated = Strings.GetSafeBool(dr["Estimated"]);
                            task.DurationType = (TaskItem.DurationTypes)Strings.GetSafeInteger(dr["DurationType"], 0);
                            task.Duration = TimeSpan.FromDays(Strings.GetSafeDouble(dr["DurationDays"], 0.0));
                        }

                        task.EndEdit(true, GanttItem.TaskShiftModes.fromLeft, false);   // New: Don't do any Recalculation (last parameter)
                        
                        tasks.Add(task);
                    }
                    dr.Close();


                    foreach (TaskItem task in tasks)
                    {
                        // ** TaskRelations **
                        dr = this.ExecuteSQL("select * from TaskRelations WHERE (TaskKey=" + Strings.ConvertNullSQL(task.Key) + ")");
                        while (dr.Read())
                        {
                            TaskRelation rel = new TaskRelation();

                            rel.DelayDays = Strings.GetSafeInteger(dr["DelayDays"], 0);
                            rel.DelayDurationType = (TaskItem.DurationTypes)Strings.GetSafeInteger(dr["DelayDurationType"], 0);
                            rel.TaskRelationMode = (TaskRelationModes)Strings.GetSafeInteger(dr["TaskRelationMode"], 0);
                            rel.TaskRelationType = (TaskRelationTypes)Strings.GetSafeInteger(dr["TaskRelationType"], 0);                            
                            rel.TaskKey = Strings.GetSafeString(dr["DependingTaskKey"]);
                            
                            task.Dependencies.Add(rel);                                                       
                        }
                        dr.Close();

                        // ** ResourceRelations **
                        dr = this.ExecuteSQL("select * from ResourceRelations WHERE (TaskKey=" + Strings.ConvertNullSQL(task.Key) + ")");
                        while (dr.Read())
                        {
                            ResourceRelation rel = new ResourceRelation();

                            rel.ProposedUnit = Strings.GetSafeDouble(dr["ProposedUnit"], -1.0);
                            rel.Unit = Strings.GetSafeDouble(dr["Unit"], 1.0);
                            
                            rel.Responsible = Strings.GetSafeBool(dr["Responsible"]);
                            rel.ResourceKey = Strings.GetSafeString(dr["ResourceKey"]);

                            task.Resources.Add(rel);                            
                        }
                        dr.Close();


                        // ** Works **
                        task.Works.Clear();     // By editing the task (see above), a default Workitem was created that must be removed

                        dr = this.ExecuteSQL("select * from Works WHERE (TaskKey=" + Strings.ConvertNullSQL(task.Key) + ")");
                        while (dr.Read())
                        {
                            WorkItem work = new WorkItem();

                            work.Key = Strings.GetSafeString(dr["ItemKey"]);
                            work.Text = Strings.GetSafeString(dr["ItemText"]);
                            work.Description = Strings.GetSafeString(dr["Description"]);

                            work.ProposedWorkHours = Strings.GetSafeDouble(dr["ProposedWorkHours"], -1.0);
                            work.WorkHours = Strings.GetSafeDouble(dr["WorkHours"], 0.0);                            

                            work.SkillKey = Strings.GetSafeString(dr["SkillKey"]);
                            work.MaxEntities = Strings.GetSafeInteger(dr["MaxEntities"], 0);

                            task.Works.Add(work);
                        }
                        dr.Close();

                        // PreferredResources
                        foreach (WorkItem work in task.Works)
                        {
                            dr = this.ExecuteSQL("select * from WorkPreferredResources WHERE (WorkKey=" + Strings.ConvertNullSQL(work.Key) + ")");

                            while (dr.Read())
                            {                                                                
                                work.AddPreferredResourceKey(Strings.GetSafeString(dr["ResourceKey"]));
                            }

                            dr.Close();
                        }
                    }                    
                }
                
                // Calculate MinDate
                DateTime dtMin = DateTime.MaxValue;

                if (tasks != null)
                {
                    foreach (TaskItem task in tasks)
                    {
                        if (task.StartDate < dtMin)
                            dtMin = task.StartDate;
                    }
                }

                if (dtMin > DateTime.Now)
                    dtMin = DateTime.Now;

                // ** ResourceAllocations **
                foreach (ResourceItem resource in resources)
                {   
                    // important change 30.3.2011
                    dr = this.ExecuteSQL("select * from ResourceAllocations WHERE (ResourceKey=" + Strings.ConvertNullSQL(resource.Key) + ") AND (EndDate >= " + Strings.SQLDateTime(dtMin, true) + ")");

                    while (dr.Read())
                    {
                        ResourceAllocation ra = new ResourceAllocation();
                        
                        ra.TaskKey = Strings.GetSafeString(dr["TaskKey"]);
                        ra.TaskCalendarKey = Strings.GetSafeString(dr["TaskCalendarKey"]);
                        ra.TaskCalendarCombinationMode = (KS.Gantt.Calendars.CalendarCombinationModes)Strings.GetSafeInteger(dr["TaskCalendarCombinationMode"], 0);
                        ra.ProjectKey = Strings.GetSafeString(dr["ProjectKey"]);
                        ra.StartDate = Strings.GetSafeDateTime(dr["StartDate"], DateTime.MinValue);
                        ra.EndDate = Strings.GetSafeDateTime(dr["EndDate"], DateTime.MinValue);
                        ra.Unit = Strings.GetSafeDouble(dr["Unit"], 0.0);
                        ra.TaskText = Strings.GetSafeString(dr["TaskText"]);
                        ra.ProjectText = Strings.GetSafeString(dr["ProjectText"]);

                        resource.ResourceAllocations.Add(ra);
                    }

                    dr.Close();
                }

                this.CommitTransaction();
            }
            catch (Exception ex)
            {
                // Transaction can only be reset when all DataReaders are disposed
                if (dr != null)
                {
                    if (!dr.IsClosed)
                        dr.Close();

                    dr.Dispose();
                    dr = null;
                }

                try
                {
                    this.RollbackTransaction();
                }
                catch
                {
                }

                gantt.ResumeItemLayout();
                
                throw ex;
            }
            finally
            {
                if (dr != null)
                {
                    if (!dr.IsClosed)
                        dr.Close();

                    dr.Dispose();
                    dr = null;
                }
            }                                    

            // >>>> Copy new values to gantt >>>>
            gantt.ClearData();

            if (project != null)
            {
                project.CopyTo(gantt.Project);
                gantt.Project.FinalizeDeSerialization(gantt);
            }

            if (calendars != null)
            {
                foreach (KS.Gantt.Calendars.Calendar calendar in calendars)
                {
                    if (gantt.CalendarList.ContainsKey(calendar.Key))
                    {
                        calendar.CalendarList = gantt.CalendarList;
                        calendar.CopyTo(gantt.CalendarList.FindItemByKey(calendar.Key));
                    }
                    else
                        gantt.CalendarList.Add(calendar);
                }
                calendars.Clear();
                gantt.CalendarList.FinalizeDeSerialization(gantt);
            }

            if (skills != null)
            {
                foreach (SkillItem skill in skills)
                {
                    if (gantt.Skills.ContainsKey(skill.Key))
                    {
                        skill.CopyTo(gantt.Skills.FindItemByKey(skill.Key));
                    }
                    else
                        gantt.Skills.Add(skill);
                }
                skills.Clear();
                gantt.Skills.FinalizeDeSerialization(gantt);
            }

            if (resources != null)
            {
                foreach (ResourceItem resource in resources)
                {
                    if (gantt.Resources.ContainsKey(resource.Key))
                    {
                        resource.CopyTo(gantt.Resources.FindItemByKey(resource.Key));
                    }
                    else
                        gantt.Resources.Add(resource);
                }
                resources.Clear();
                gantt.Resources.FinalizeDeSerialization(gantt);
            }

            if (groups != null)
            {
                gantt.Groups.AssignDefaultFormatStyleOnAdd = false;
                foreach (GroupItem group in groups)
                {
                    if (gantt.Groups.ContainsKey(group.Key))
                    {
                        group.CopyTo(gantt.Groups.FindItemByKey(group.Key));
                    }
                    else
                        gantt.Groups.Add(group);
                }
                groups.Clear();
                gantt.Groups.FinalizeDeSerialization(gantt);
            }

            if (tasks != null)
            {
                gantt.Tasks.AssignDefaultFormatStyleOnAdd = false;
                foreach (TaskItem task in tasks)
                {
                    if (gantt.Tasks.ContainsKey(task.Key))
                    {
                        task.CopyTo(gantt.Tasks.FindItemByKey(task.Key));
                    }
                    else
                        gantt.Tasks.Add(task);
                }
                tasks.Clear();
                gantt.Tasks.FinalizeDeSerialization(gantt);
            }
            
            gantt.FixConsistency();            

            gantt.ViewMode = Gantt.ViewModes.Tasks;

            gantt.RecalculateTasks();
            gantt.AutoMoveItems();

            gantt.ResumeItemLayout();

            gantt.HistoryClear();
            gantt.ResetModified();
        }

        public void RefreshResourceAllocationsFromOtherProjects(Gantt gantt)
        {            
            if (ProjectKey != gantt.Project.Key)
                throw new InvalidOperationException("ProjectKey != gantt.Project.Key");

            if (String.IsNullOrEmpty(gantt.Project.Key))
                throw new InvalidOperationException("gantt.Project.Key is null");

            if (!IsConnected)
                Connect();            

            System.Data.SqlClient.SqlDataReader dr = null;

            try
            {
                this.BeginTransaction();                

                // Calculate MinDate                
                gantt.Tasks.CalcMinMaxDate();
                DateTime dtMin = gantt.Tasks.MinDate;
                
                if (dtMin > DateTime.Now)
                    dtMin = DateTime.Now;

                // ** ResourceAllocations **
                foreach (ResourceItem resource in gantt.Resources)
                {
                    resource.ResourceAllocations.ClearForeignAllocations(ProjectKey);

                    dr = this.ExecuteSQL("select * from ResourceAllocations WHERE (ResourceKey=" + Strings.ConvertNullSQL(resource.Key) + ") AND ((ProjectKey IS NULL) Or (ProjectKey<>" + Strings.ConvertNullSQL(ProjectKey) + ")) AND (EndDate >= " + Strings.SQLDateTime(dtMin, true) + ")");

                    while (dr.Read())
                    {
                        ResourceAllocation ra = new ResourceAllocation();

                        ra.TaskKey = Strings.GetSafeString(dr["TaskKey"]);
                        ra.TaskCalendarKey = Strings.GetSafeString(dr["TaskCalendarKey"]);
                        ra.TaskCalendarCombinationMode = (KS.Gantt.Calendars.CalendarCombinationModes)Strings.GetSafeInteger(dr["TaskCalendarCombinationMode"], 0);
                        ra.ProjectKey = Strings.GetSafeString(dr["ProjectKey"]);
                        ra.StartDate = Strings.GetSafeDateTime(dr["StartDate"], DateTime.MinValue);
                        ra.EndDate = Strings.GetSafeDateTime(dr["EndDate"], DateTime.MinValue);
                        ra.Unit = Strings.GetSafeDouble(dr["Unit"], 0.0);
                        ra.TaskText = Strings.GetSafeString(dr["TaskText"]);
                        ra.ProjectText = Strings.GetSafeString(dr["ProjectText"]);

                        resource.ResourceAllocations.Add(ra);
                    }

                    dr.Close();

                    resource.ResourceAllocations.Sort();
                }

                this.CommitTransaction();
            }
            catch (Exception ex)
            {
                // Transaction can only be reset when all DataReaders are disposed
                if (dr != null)
                {
                    if (!dr.IsClosed)
                        dr.Close();

                    dr.Dispose();
                    dr = null;
                }

                try
                {
                    this.RollbackTransaction();
                }
                catch
                {
                }                

                throw ex;
            }
            finally
            {
                if (dr != null)
                {
                    if (!dr.IsClosed)
                        dr.Close();

                    dr.Dispose();
                    dr = null;
                }
            }
                        
        }

        /// <summary>
        /// Drop a project on the server and reset the gantt
        /// </summary>
        /// <param name="gantt"></param>
        /// <param name="projectKey"></param>
        public void DropProject(Gantt gantt, string projectKey)
        {
            try
            {
                this.BeginTransaction();
                ExecuteUpdateSQL("delete from Projects WHERE ItemKey=" + Strings.ConvertNullSQL(projectKey));
                this.CommitTransaction();                
                gantt.ClearData();
            }
            catch (Exception ex)
            {
                this.RollbackTransaction();
                throw ex;
            }            
        }

        /// <summary>
        /// Returns the number of projects
        /// </summary>
        public int ProjectCount
        {
            get
            {
                int Count = 0;

                try
                {
                    Count = Strings.GetSafeInteger(ExecuteScalar("select count(ItemKey) from Projects"), 0);
                }
                catch
                {
                    Count = 0;
                }

                return Count;
            }
        }

        public bool CheckServerVersion()
        {
            if (DBConn.State != ConnectionState.Open)
                return false;

            try
            {
                string[] version = DBConn.ServerVersion.Split('.');

                if (version.Length == 0)
                    return false;

                int MajorVersion = 0;
                if (Int32.TryParse(version[0], out MajorVersion))
                    return MajorVersion >= 9;
            }
            catch (Exception)
            {
                return false;
            }            

            return false;
        }

        public bool CheckDatabaseVersion()
        {
            if (DBConn.State != ConnectionState.Open)
                return false;

            DataTable tblTables = DBConn.GetSchema("Tables");

            bool bFoundProjects = false;
            bool bFoundTasks = false;
            bool bFoundGroups = false;
            bool bFoundResources = false;
            bool bFoundCalendars = false;
            bool bFoundResourceSkills = false;


            string strTable;
            foreach (DataRow dr in tblTables.Rows)
            {
                strTable = Strings.GetSafeString(dr["table_name"]);

                switch (strTable.ToLower())
                {
                    case "projects":
                        bFoundProjects = true;
                        break;

                    case "tasks":
                        bFoundTasks = true;
                        break;

                    case "groups":
                        bFoundGroups = true;
                        break;

                    case "resources":
                        bFoundResources = true;
                        break;

                    case "calendars":
                        bFoundCalendars = true;
                        break;

                    case "resourceskills":
                        bFoundResourceSkills = true;
                        break;                    
                }                
            }

            if (!(bFoundProjects && bFoundTasks && bFoundGroups && bFoundResources && bFoundCalendars))
            {
                throw new Exception(Properties.Resources.SQLInvalidDatabase);
            }

            bool bResourceBarColorTolerance = false;
            bool bProposedUnit = false;
            bool bProposedWorkHours = false;
            bool bShowHappinessIcon = false;

            DataTable tblColumns = DBConn.GetSchema("Columns");
            string strColumn;

            foreach (DataRow dr in tblColumns.Rows)
            {
                strColumn = Strings.GetSafeString(dr["column_name"]);

                switch (strColumn.ToLower())
                {
                    case "resourcebarcolortolerance":
                        bResourceBarColorTolerance = true;
                        break;

                    case "proposedunit":
                        bProposedUnit = true;
                        break;

                    case "proposedworkhours":
                        bProposedWorkHours = true;
                        break;                    

                    case "showhappinessicon":
                        bShowHappinessIcon = true;
                        break;
                }
            }

            if (!bFoundResourceSkills || !bResourceBarColorTolerance || !bProposedUnit || !bProposedWorkHours || !bShowHappinessIcon)
            {
                if (Program.QuestionMsgBox(Properties.Resources.SQLDatabaseNeedsUpdate) != System.Windows.Forms.DialogResult.Yes)
                    return false;

                try
                {
                    RunSqlScript(m_SqlUpdateScriptPath);
                    Program.InfoMsgBox(Properties.Resources.SQLDatabaseUpdateSuccessfull);
                }
                catch (Exception ex)
                {                    
                    throw ex;
                }                
            }                       

            return true;
        }

        public void RunSqlScript(string FileName)
        {
            if (!Strings.FileExists(FileName))
            {
                throw new Exception("SqlScriptPath file not found: " + FileName);
            }

            string strSQL = "";
            System.IO.StreamReader SqlFile = null;

            try
            {
                SqlFile = new System.IO.StreamReader(FileName);
                strSQL = SqlFile.ReadToEnd();
                SqlFile.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (SqlFile != null)
                {
                    SqlFile.Dispose();
                    SqlFile = null;
                }
            }

            if (strSQL != "")
            {
                try
                {
                    Program.CursorWait();

                    string[] strCommands = Strings.Split(strSQL, "GO");

                    long iAffected = 0;

                    foreach (string strCommand in strCommands)
                    {
                        if (strCommand.Trim() != "")
                        {
                            if (ScriptStatus != null)
                                ScriptStatus(this, new SqlDBTextEventArgs(strCommand + "\r\n"));

                            iAffected = this.ExecuteUpdateSQL(strCommand);                            
                        }
                    }                    
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    Program.CursorDefault();
                }                
            }
        }        
    }
}
